|
Lines 73-85
BEGIN {
Link Here
|
| 73 |
&ReNewSubscription &GetLateOrMissingIssues |
73 |
&ReNewSubscription &GetLateOrMissingIssues |
| 74 |
&GetSerialInformation &AddItem2Serial |
74 |
&GetSerialInformation &AddItem2Serial |
| 75 |
&PrepareSerialsData &GetNextExpected &ModNextExpected |
75 |
&PrepareSerialsData &GetNextExpected &ModNextExpected |
|
|
76 |
&GetSerial &GetSerialItemnumber |
| 76 |
|
77 |
|
| 77 |
&UpdateClaimdateIssues |
78 |
&UpdateClaimdateIssues |
| 78 |
&GetSuppliersWithLateIssues &getsupplierbyserialid |
79 |
&GetSuppliersWithLateIssues &getsupplierbyserialid |
| 79 |
&GetDistributedTo &SetDistributedTo |
80 |
&GetDistributedTo &SetDistributedTo |
| 80 |
&getroutinglist &delroutingmember &addroutingmember |
81 |
&updateClaim |
| 81 |
&reorder_members |
|
|
| 82 |
&check_routing &updateClaim |
| 83 |
&CountIssues |
82 |
&CountIssues |
| 84 |
HasItems |
83 |
HasItems |
| 85 |
&GetSubscriptionsFromBorrower |
84 |
&GetSubscriptionsFromBorrower |
|
Lines 160-165
sub GetSubscriptionHistoryFromSubscriptionId {
Link Here
|
| 160 |
return $results; |
159 |
return $results; |
| 161 |
} |
160 |
} |
| 162 |
|
161 |
|
|
|
162 |
=head2 GetSerial |
| 163 |
|
| 164 |
my $serial = &GetSerial($serialid); |
| 165 |
|
| 166 |
This sub returns serial informations (ie. in serial table) for given $serialid |
| 167 |
It returns a hashref where each key is a sql column. |
| 168 |
|
| 169 |
=cut |
| 170 |
|
| 171 |
sub GetSerial { |
| 172 |
my ($serialid) = @_; |
| 173 |
|
| 174 |
return unless $serialid; |
| 175 |
|
| 176 |
my $dbh = C4::Context->dbh; |
| 177 |
my $query = qq{ |
| 178 |
SELECT * |
| 179 |
FROM serial |
| 180 |
WHERE serialid = ? |
| 181 |
}; |
| 182 |
my $sth = $dbh->prepare($query); |
| 183 |
$sth->execute($serialid); |
| 184 |
return $sth->fetchrow_hashref; |
| 185 |
} |
| 186 |
|
| 187 |
=head2 GetSerialItemnumber |
| 188 |
|
| 189 |
my $itemnumber = GetSerialItemnumber($serialid); |
| 190 |
|
| 191 |
Returns the itemnumber associated to $serialid or undef if there is no item. |
| 192 |
|
| 193 |
=cut |
| 194 |
|
| 195 |
sub GetSerialItemnumber { |
| 196 |
my ($serialid) = @_; |
| 197 |
|
| 198 |
return unless $serialid; |
| 199 |
my $itemnumber; |
| 200 |
|
| 201 |
my $dbh = C4::Context->dbh; |
| 202 |
my $query = qq{ |
| 203 |
SELECT itemnumber |
| 204 |
FROM serialitems |
| 205 |
WHERE serialid = ? |
| 206 |
}; |
| 207 |
my $sth = $dbh->prepare($query); |
| 208 |
my $rv = $sth->execute($serialid); |
| 209 |
if ($rv) { |
| 210 |
my $result = $sth->fetchrow_hashref; |
| 211 |
$itemnumber = $result->{itemnumber}; |
| 212 |
} |
| 213 |
return $itemnumber; |
| 214 |
} |
| 215 |
|
| 163 |
=head2 GetSerialStatusFromSerialId |
216 |
=head2 GetSerialStatusFromSerialId |
| 164 |
|
217 |
|
| 165 |
$sth = GetSerialStatusFromSerialId(); |
218 |
$sth = GetSerialStatusFromSerialId(); |
|
Lines 662-668
sub GetSerials {
Link Here
|
| 662 |
$count = 5 unless ($count); |
715 |
$count = 5 unless ($count); |
| 663 |
my @serials; |
716 |
my @serials; |
| 664 |
my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES, NOT_ISSUED ) ); |
717 |
my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES, NOT_ISSUED ) ); |
| 665 |
my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes |
718 |
my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes |
| 666 |
FROM serial |
719 |
FROM serial |
| 667 |
WHERE subscriptionid = ? AND status NOT IN ( $statuses ) |
720 |
WHERE subscriptionid = ? AND status NOT IN ( $statuses ) |
| 668 |
ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC"; |
721 |
ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC"; |
|
Lines 682-688
sub GetSerials {
Link Here
|
| 682 |
} |
735 |
} |
| 683 |
|
736 |
|
| 684 |
# OK, now add the last 5 issues arrives/missing |
737 |
# OK, now add the last 5 issues arrives/missing |
| 685 |
$query = "SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes |
738 |
$query = "SELECT serialid,serialseq, status, planneddate, publisheddate,notes |
| 686 |
FROM serial |
739 |
FROM serial |
| 687 |
WHERE subscriptionid = ? |
740 |
WHERE subscriptionid = ? |
| 688 |
AND status IN ( $statuses ) |
741 |
AND status IN ( $statuses ) |
|
Lines 731-737
sub GetSerials2 {
Link Here
|
| 731 |
|
784 |
|
| 732 |
my $dbh = C4::Context->dbh; |
785 |
my $dbh = C4::Context->dbh; |
| 733 |
my $query = qq| |
786 |
my $query = qq| |
| 734 |
SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes |
787 |
SELECT serialid,serialseq, status, planneddate, publisheddate,notes |
| 735 |
FROM serial |
788 |
FROM serial |
| 736 |
WHERE subscriptionid=$subscription AND status IN ($statuses_string) |
789 |
WHERE subscriptionid=$subscription AND status IN ($statuses_string) |
| 737 |
ORDER BY publisheddate,serialid DESC |
790 |
ORDER BY publisheddate,serialid DESC |
|
Lines 1990-2150
sub getsupplierbyserialid {
Link Here
|
| 1990 |
return $result; |
2043 |
return $result; |
| 1991 |
} |
2044 |
} |
| 1992 |
|
2045 |
|
| 1993 |
=head2 check_routing |
|
|
| 1994 |
|
| 1995 |
$result = &check_routing($subscriptionid) |
| 1996 |
|
| 1997 |
this function checks to see if a serial has a routing list and returns the count of routingid |
| 1998 |
used to show either an 'add' or 'edit' link |
| 1999 |
|
| 2000 |
=cut |
| 2001 |
|
| 2002 |
sub check_routing { |
| 2003 |
my ($subscriptionid) = @_; |
| 2004 |
|
| 2005 |
return unless ($subscriptionid); |
| 2006 |
|
| 2007 |
my $dbh = C4::Context->dbh; |
| 2008 |
my $sth = $dbh->prepare( |
| 2009 |
"SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist |
| 2010 |
ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid |
| 2011 |
WHERE subscription.subscriptionid = ? ORDER BY ranking ASC |
| 2012 |
" |
| 2013 |
); |
| 2014 |
$sth->execute($subscriptionid); |
| 2015 |
my $line = $sth->fetchrow_hashref; |
| 2016 |
my $result = $line->{'routingids'}; |
| 2017 |
return $result; |
| 2018 |
} |
| 2019 |
|
| 2020 |
=head2 addroutingmember |
| 2021 |
|
| 2022 |
addroutingmember($borrowernumber,$subscriptionid) |
| 2023 |
|
| 2024 |
this function takes a borrowernumber and subscriptionid and adds the member to the |
| 2025 |
routing list for that serial subscription and gives them a rank on the list |
| 2026 |
of either 1 or highest current rank + 1 |
| 2027 |
|
| 2028 |
=cut |
| 2029 |
|
| 2030 |
sub addroutingmember { |
| 2031 |
my ( $borrowernumber, $subscriptionid ) = @_; |
| 2032 |
|
| 2033 |
return unless ($borrowernumber and $subscriptionid); |
| 2034 |
|
| 2035 |
my $rank; |
| 2036 |
my $dbh = C4::Context->dbh; |
| 2037 |
my $sth = $dbh->prepare( "SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?" ); |
| 2038 |
$sth->execute($subscriptionid); |
| 2039 |
while ( my $line = $sth->fetchrow_hashref ) { |
| 2040 |
if ( $line->{'rank'} > 0 ) { |
| 2041 |
$rank = $line->{'rank'} + 1; |
| 2042 |
} else { |
| 2043 |
$rank = 1; |
| 2044 |
} |
| 2045 |
} |
| 2046 |
$sth = $dbh->prepare( "INSERT INTO subscriptionroutinglist (subscriptionid,borrowernumber,ranking) VALUES (?,?,?)" ); |
| 2047 |
$sth->execute( $subscriptionid, $borrowernumber, $rank ); |
| 2048 |
} |
| 2049 |
|
| 2050 |
=head2 reorder_members |
| 2051 |
|
| 2052 |
reorder_members($subscriptionid,$routingid,$rank) |
| 2053 |
|
| 2054 |
this function is used to reorder the routing list |
| 2055 |
|
| 2056 |
it takes the routingid of the member one wants to re-rank and the rank it is to move to |
| 2057 |
- it gets all members on list puts their routingid's into an array |
| 2058 |
- removes the one in the array that is $routingid |
| 2059 |
- then reinjects $routingid at point indicated by $rank |
| 2060 |
- then update the database with the routingids in the new order |
| 2061 |
|
| 2062 |
=cut |
| 2063 |
|
| 2064 |
sub reorder_members { |
| 2065 |
my ( $subscriptionid, $routingid, $rank ) = @_; |
| 2066 |
my $dbh = C4::Context->dbh; |
| 2067 |
my $sth = $dbh->prepare( "SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC" ); |
| 2068 |
$sth->execute($subscriptionid); |
| 2069 |
my @result; |
| 2070 |
while ( my $line = $sth->fetchrow_hashref ) { |
| 2071 |
push( @result, $line->{'routingid'} ); |
| 2072 |
} |
| 2073 |
|
| 2074 |
# To find the matching index |
| 2075 |
my $i; |
| 2076 |
my $key = -1; # to allow for 0 being a valid response |
| 2077 |
for ( $i = 0 ; $i < @result ; $i++ ) { |
| 2078 |
if ( $routingid == $result[$i] ) { |
| 2079 |
$key = $i; # save the index |
| 2080 |
last; |
| 2081 |
} |
| 2082 |
} |
| 2083 |
|
| 2084 |
# if index exists in array then move it to new position |
| 2085 |
if ( $key > -1 && $rank > 0 ) { |
| 2086 |
my $new_rank = $rank - 1; # $new_rank is what you want the new index to be in the array |
| 2087 |
my $moving_item = splice( @result, $key, 1 ); |
| 2088 |
splice( @result, $new_rank, 0, $moving_item ); |
| 2089 |
} |
| 2090 |
for ( my $j = 0 ; $j < @result ; $j++ ) { |
| 2091 |
my $sth = $dbh->prepare( "UPDATE subscriptionroutinglist SET ranking = '" . ( $j + 1 ) . "' WHERE routingid = '" . $result[$j] . "'" ); |
| 2092 |
$sth->execute; |
| 2093 |
} |
| 2094 |
return; |
| 2095 |
} |
| 2096 |
|
| 2097 |
=head2 delroutingmember |
| 2098 |
|
| 2099 |
delroutingmember($routingid,$subscriptionid) |
| 2100 |
|
| 2101 |
this function either deletes one member from routing list if $routingid exists otherwise |
| 2102 |
deletes all members from the routing list |
| 2103 |
|
| 2104 |
=cut |
| 2105 |
|
| 2106 |
sub delroutingmember { |
| 2107 |
|
| 2108 |
# if $routingid exists then deletes that row otherwise deletes all with $subscriptionid |
| 2109 |
my ( $routingid, $subscriptionid ) = @_; |
| 2110 |
my $dbh = C4::Context->dbh; |
| 2111 |
if ($routingid) { |
| 2112 |
my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?"); |
| 2113 |
$sth->execute($routingid); |
| 2114 |
reorder_members( $subscriptionid, $routingid ); |
| 2115 |
} else { |
| 2116 |
my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?"); |
| 2117 |
$sth->execute($subscriptionid); |
| 2118 |
} |
| 2119 |
return; |
| 2120 |
} |
| 2121 |
|
| 2122 |
=head2 getroutinglist |
| 2123 |
|
| 2124 |
@routinglist = getroutinglist($subscriptionid) |
| 2125 |
|
| 2126 |
this gets the info from the subscriptionroutinglist for $subscriptionid |
| 2127 |
|
| 2128 |
return : |
| 2129 |
the routinglist as an array. Each element of the array contains a hash_ref containing |
| 2130 |
routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription |
| 2131 |
|
| 2132 |
=cut |
| 2133 |
|
| 2134 |
sub getroutinglist { |
| 2135 |
my ($subscriptionid) = @_; |
| 2136 |
my $dbh = C4::Context->dbh; |
| 2137 |
my $sth = $dbh->prepare( |
| 2138 |
'SELECT routingid, borrowernumber, ranking, biblionumber |
| 2139 |
FROM subscription |
| 2140 |
JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid |
| 2141 |
WHERE subscription.subscriptionid = ? ORDER BY ranking ASC' |
| 2142 |
); |
| 2143 |
$sth->execute($subscriptionid); |
| 2144 |
my $routinglist = $sth->fetchall_arrayref({}); |
| 2145 |
return @{$routinglist}; |
| 2146 |
} |
| 2147 |
|
| 2148 |
=head2 countissuesfrom |
2046 |
=head2 countissuesfrom |
| 2149 |
|
2047 |
|
| 2150 |
$result = countissuesfrom($subscriptionid,$startdate) |
2048 |
$result = countissuesfrom($subscriptionid,$startdate) |
|
Lines 2282-2302
sub GetSubscriptionsFromBorrower {
Link Here
|
| 2282 |
my ($borrowernumber) = @_; |
2180 |
my ($borrowernumber) = @_; |
| 2283 |
my $dbh = C4::Context->dbh; |
2181 |
my $dbh = C4::Context->dbh; |
| 2284 |
my $sth = $dbh->prepare( |
2182 |
my $sth = $dbh->prepare( |
| 2285 |
"SELECT subscription.subscriptionid, biblio.title |
2183 |
"SELECT subscriptionroutinglist.*, biblio.title AS bibliotitle |
| 2286 |
FROM subscription |
2184 |
FROM subscriptionroutinglist |
|
|
2185 |
JOIN subscriptionrouting ON (subscriptionroutinglist.routinglistid = subscriptionrouting.routinglistid) |
| 2186 |
JOIN subscription ON (subscriptionroutinglist.subscriptionid = subscription.subscriptionid) |
| 2287 |
JOIN biblio ON biblio.biblionumber = subscription.biblionumber |
2187 |
JOIN biblio ON biblio.biblionumber = subscription.biblionumber |
| 2288 |
JOIN subscriptionroutinglist USING (subscriptionid) |
2188 |
WHERE subscriptionrouting.borrowernumber = ? ORDER BY title ASC |
| 2289 |
WHERE subscriptionroutinglist.borrowernumber = ? ORDER BY title ASC |
|
|
| 2290 |
" |
2189 |
" |
| 2291 |
); |
2190 |
); |
| 2292 |
$sth->execute($borrowernumber); |
2191 |
$sth->execute($borrowernumber); |
| 2293 |
my @routinglist; |
2192 |
my $routinglists = $sth->fetchall_arrayref({}); |
| 2294 |
my $count = 0; |
2193 |
return $routinglists ? @$routinglists : (); |
| 2295 |
while ( my $line = $sth->fetchrow_hashref ) { |
|
|
| 2296 |
$count++; |
| 2297 |
push( @routinglist, $line ); |
| 2298 |
} |
| 2299 |
return ( $count, @routinglist ); |
| 2300 |
} |
2194 |
} |
| 2301 |
|
2195 |
|
| 2302 |
|
2196 |
|