Attachment #10582 for bug #8296

View | Details | Raw Unified | Return to bug 8296
Collapse All | Expand All




            serial.serialseq,
            serial.planneddate, 
            serial.publisheddate, 
            serial.publisheddatetext,
            serial.status, 
            serial.notes as notes,
            year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,

            serial.serialseq,
            serial.planneddate, 
            serial.publisheddate, 
            serial.publisheddatetext,
            serial.status, 
            serial.notes as notes,
            year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,

    my $counter = 0;
    $count = 5 unless ($count);
    my @serials;
    my $query = "SELECT serialid,serialseq, status, publisheddate,
        publisheddatetext, planneddate,notes, routingnotes
                        FROM   serial
                        WHERE  subscriptionid = ? AND status NOT IN (2,4,5) 
                        ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";

    }

    # OK, now add the last 5 issues arrives/missing
    $query = "SELECT   serialid,serialseq, status, planneddate, publisheddate,
        publisheddatetext, notes, routingnotes
       FROM     serial
       WHERE    subscriptionid = ?
       AND      (status in (2,4,5))

    my ( $subscription, $status ) = @_;
    my $dbh   = C4::Context->dbh;
    my $query = qq|
                 SELECT serialid,serialseq, status, planneddate, publisheddate,
                    publisheddatetext, notes, routingnotes
                 FROM     serial 
                 WHERE    subscriptionid=$subscription AND status IN ($status)
                 ORDER BY publisheddate,serialid DESC


=head2 ModSerialStatus

    ModSerialStatus($serialid, $serialseq, $planneddate, $publisheddate,
        $publisheddatetext, $status, $notes);

This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
Note : if we change from "waited" to something else,then we will have to create a new "waited" entry

=cut

sub ModSerialStatus {
    my ($serialid, $serialseq, $planneddate, $publisheddate, $publisheddatetext,
        $status, $notes) = @_;

    #It is a usual serial
    # 1st, get previous status :

        DelIssue( {'serialid'=>$serialid, 'subscriptionid'=>$subscriptionid,'serialseq'=>$serialseq} );
    }
    else {
        my $query = '
            UPDATE serial
            SET serialseq = ?, publisheddate = ?, publisheddatetext = ?,
                planneddate = ?, status = ?, notes = ?
            WHERE  serialid = ?
        ';
        $sth = $dbh->prepare($query);
        $sth->execute( $serialseq, $publisheddate, $publisheddatetext,
            $planneddate, $status, $notes, $serialid );
        $query = "SELECT * FROM   subscription WHERE  subscriptionid = ?";
        $sth   = $dbh->prepare($query);
        $sth->execute($subscriptionid);

=cut

sub NewIssue {
    my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate,
        $publisheddate, $publisheddatetext, $notes ) = @_;
    ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?

    my $dbh   = C4::Context->dbh;
    my $query = qq|
        INSERT INTO serial (serialseq, subscriptionid, biblionumber, status,
            publisheddate, publisheddatetext, planneddate, notes)
        VALUES (?,?,?,?,?,?,?,?)
    |;
    my $sth = $dbh->prepare($query);
    $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status,
        $publisheddate, $publisheddatetext, $planneddate, $notes );
    my $serialid = $dbh->{'mysql_insertid'};
    $query = qq|
        SELECT missinglist,recievedlist

Lines 1768-1773 CREATE TABLE `serial` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
1768	`planneddate` date default NULL,	1768	`planneddate` date default NULL,
1769	`notes` text,	1769	`notes` text,
1770	`publisheddate` date default NULL,	1770	`publisheddate` date default NULL,
		1771	publisheddatetext varchar(100) default NULL,
1771	`itemnumber` text default NULL,	1772	`itemnumber` text default NULL,
1772	`claimdate` date default NULL,	1773	`claimdate` date default NULL,
1773	`routingnotes` text,	1774	`routingnotes` text,




    SetVersion($DBversion);
}

$DBversion = "XXX";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
    $dbh->do("
        ALTER TABLE serial
        ADD COLUMN publisheddatetext VARCHAR(100) DEFAULT NULL
          AFTER publisheddate
    ");

    print "Upgrade to $DBversion done (Add serial.publisheddatetext)\n";
    SetVersion($DBversion);
}

=head1 FUNCTIONS

=head2 TableExists($table)

Lines 200-205 $(document).ready(function() { Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-collection.tt (+4 lines)
200	[% END %]	200	[% END %]
201	<th>Date published	201	<th>Date published
202	</th>	202	</th>
		203	<th>Date published (text)</th>
203	<th>Date received	204	<th>Date received
204	</th>	205	</th>
205	<th>Number	206	<th>Number
Lines 222-227 $(document).ready(function() { Link Here
222	[% serial.publisheddate %]	223	[% serial.publisheddate %]
223	</td>	224	</td>
224	<td>	225	<td>
		226	[% serial.publisheddatetext \|\| serial.publisheddate %]
		227	</td>
		228	<td>
225	[% serial.planneddate %]	229	[% serial.planneddate %]
226	</td>	230	</td>
227	<td>	231	<td>

Lines 198-203 $(document).ready(function() { Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-edit.tt (+7 lines)
198	<tr>	198	<tr>
199	<th>Numbered</th>	199	<th>Numbered</th>
200	<th>Published on</th>	200	<th>Published on</th>
		201	<th>Published on (text)</th>
201	<th>Expected on</th>	202	<th>Expected on</th>
202	<th>Status</th>	203	<th>Status</th>
203	<th>Notes</th>	204	<th>Notes</th>
Lines 219-224 $(document).ready(function() { Link Here
219	<input type="text" name="publisheddate" value="[% serialslis.publisheddate %]" size="10" maxlength="15" />	220	<input type="text" name="publisheddate" value="[% serialslis.publisheddate %]" size="10" maxlength="15" />
220	</td>	221	</td>
221	<td>	222	<td>
		223	<input type="text" name="publisheddatetext" value="[% serialslis.publisheddatetext \|\| serialslis.publisheddate %]" size="10" maxlength="15" />
		224	</td>
		225	<td>
222	<input type="text" id="expecteddate" name="planneddate" value="[% serialslis.planneddate %]" size="10" maxlength="15" />	226	<input type="text" id="expecteddate" name="planneddate" value="[% serialslis.planneddate %]" size="10" maxlength="15" />
223	</td>	227	</td>
224	<td>	228	<td>
Lines 331-336 $(document).ready(function() { Link Here
331	<input type="text" name="publisheddate" value="[% newserialloo.publisheddate %]" size="10" maxlength="15" />	335	<input type="text" name="publisheddate" value="[% newserialloo.publisheddate %]" size="10" maxlength="15" />
332	</td>	336	</td>
333	<td>	337	<td>
		338	<input type="text" name="publisheddatetext" value="[% newserialloo.publisheddatetext \|\| newserialloo.publisheddate %]" size="10" maxlength="15" />
		339	</td>
		340	<td>
334	<input type="text" name="planneddate" id="supexpecteddate" value="[% newserialloo.planneddate %]" size="10" maxlength="15" />	341	<input type="text" name="planneddate" id="supexpecteddate" value="[% newserialloo.planneddate %]" size="10" maxlength="15" />
335	</td>	342	</td>
336	<td>	343	<td>

Lines 326-331 $(document).ready(function() { Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/subscription-detail.tt (+6 lines)
326	<th>Issue number</th>	326	<th>Issue number</th>
327	<th>Planned date</th>	327	<th>Planned date</th>
328	<th>Published date</th>	328	<th>Published date</th>
		329	<th>Published date (text)</th>
329	<th>Status</th>	330	<th>Status</th>
330	</tr>	331	</tr>
331	[% FOREACH serialslis IN serialslist %]	332	[% FOREACH serialslis IN serialslist %]
Lines 340-345 $(document).ready(function() { Link Here
340	[% serialslis.publisheddate %]	341	[% serialslis.publisheddate %]
341	</td>	342	</td>
342	<td>	343	<td>
		344	[% serialslis.publisheddatetext
		345	\|\| serialslis.publisheddate
		346	%]
		347	</td>
		348	<td>
343	[% IF ( serialslis.status1 ) %]	349	[% IF ( serialslis.status1 ) %]
344	Expected	350	Expected
345	[% END %]	351	[% END %]

Lines 179-184 function showlayer(numlayer){ Link Here

(-)a/koha-tmpl/opac-tmpl/prog/en/modules/opac-full-serial-issues.tt (+2 lines)
179	<thead>	179	<thead>
180	<tr>	180	<tr>
181	<th>Date</th>	181	<th>Date</th>
		182	<th>Date (text)</th>
182	<th>Library</th>	183	<th>Library</th>
183	<th>Notes</th>	184	<th>Notes</th>
184	<th>Date received</th>	185	<th>Date received</th>
Lines 192-197 function showlayer(numlayer){ Link Here
192	<tr>	193	<tr>
193	<td> [% serial.publisheddate %]	194	<td> [% serial.publisheddate %]
194	</td>	195	</td>
		196	<td>[% serial.publisheddatetext \|\| serial.publisheddate %]</td>
195	<td class="libraryfilterclass"> [% serial.branchcode %]	197	<td class="libraryfilterclass"> [% serial.branchcode %]
196	</td>	198	</td>
197	<td> [% serial.notes %]	199	<td> [% serial.notes %]

Lines 82-88 while ( my $issue = $sth->fetchrow_hashref ) { Link Here

(-)a/misc/cronjobs/serialsUpdate.pl (-1 / +1 lines)
82	{	82	{
83		83
84	ModSerialStatus( $issue->{serialid}, $issue->{serialseq},	84	ModSerialStatus( $issue->{serialid}, $issue->{serialseq},
85	$issue->{planneddate}, $issue->{publisheddate},	85	$issue->{planneddate}, $issue->{publisheddate}, $issue->{publisheddatetext},
86	3, "Automatically set to late" );	86	3, "Automatically set to late" );
87	print $issue->{serialid}." update\n";	87	print $issue->{serialid}." update\n";
88	}	88	}




    my $subscriptionid = $subscriptionid[0];
    my $subscription = GetSubscription($subscriptionid);

	my $sth = $dbh->prepare("
        SELECT publisheddate, publisheddatetext, serialid, serialseq,
            planneddate
        FROM serial
        WHERE status = 1 AND subscriptionid = ?
    ");
	$sth->execute($subscriptionid);

	# modify actual expected issue, to generate the next
	if ( my $issue = $sth->fetchrow_hashref ) {
		ModSerialStatus( $issue->{serialid}, $issue->{serialseq},
            $issue->{planneddate}, $issue->{publisheddate},
            $issue->{publisheddatetext}, 3, "" );
	}else{
		my $expected = GetNextExpected($subscriptionid);
	    my (

Lines 81-86 my @serialids = $query->param('serialid'); Link Here

(-)a/serials/serials-edit.pl (-1 / +4 lines)
81	my @serialseqs = $query->param('serialseq');	81	my @serialseqs = $query->param('serialseq');
82	my @planneddates = $query->param('planneddate');	82	my @planneddates = $query->param('planneddate');
83	my @publisheddates = $query->param('publisheddate');	83	my @publisheddates = $query->param('publisheddate');
		84	my @publisheddates = $query->param('publisheddate');
		85	my @publisheddatetexts = $query->param('publisheddatetext');
84	my @status = $query->param('status');	86	my @status = $query->param('status');
85	my @notes = $query->param('notes');	87	my @notes = $query->param('notes');
86	my @subscriptionids = $query->param('subscriptionid');	88	my @subscriptionids = $query->param('subscriptionid');
Lines 226-231 if ( $op and $op eq 'serialchangestatus' ) { Link Here
226	$status[$i],	228	$status[$i],
227	$plan_date,	229	$plan_date,
228	$pub_date,	230	$pub_date,
		231	$publisheddatetexts[$i],
229	$notes[$i]	232	$notes[$i]
230	);	233	);
231	}	234	}
Lines 236-241 if ( $op and $op eq 'serialchangestatus' ) { Link Here
236	$serialseqs[$i],	239	$serialseqs[$i],
237	$plan_date,	240	$plan_date,
238	$pub_date,	241	$pub_date,
		242	$publisheddatetexts[$i],
239	$status[$i],	243	$status[$i],
240	$notes[$i]	244	$notes[$i]
241	);	245	);
242	-

Return to bug 8296

Lines 352-357 sub GetFullSubscription { Link Here

(-)a/C4/Serials.pm (-13 / +27 lines)
352	serial.serialseq,	352	serial.serialseq,
353	serial.planneddate,	353	serial.planneddate,
354	serial.publisheddate,	354	serial.publisheddate,
		355	serial.publisheddatetext,
355	serial.status,	356	serial.status,
356	serial.notes as notes,	357	serial.notes as notes,
357	year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,	358	year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
Lines 513-518 sub GetFullSubscriptionsFromBiblionumber { Link Here
513	serial.serialseq,	514	serial.serialseq,
514	serial.planneddate,	515	serial.planneddate,
515	serial.publisheddate,	516	serial.publisheddate,
		517	serial.publisheddatetext,
516	serial.status,	518	serial.status,
517	serial.notes as notes,	519	serial.notes as notes,
518	year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,	520	year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
Lines 649-655 sub GetSerials { Link Here
649	my $counter = 0;	651	my $counter = 0;
650	$count = 5 unless ($count);	652	$count = 5 unless ($count);
651	my @serials;	653	my @serials;
652	my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes	654	my $query = "SELECT serialid,serialseq, status, publisheddate,
		655	publisheddatetext, planneddate,notes, routingnotes
653	FROM serial	656	FROM serial
654	WHERE subscriptionid = ? AND status NOT IN (2,4,5)	657	WHERE subscriptionid = ? AND status NOT IN (2,4,5)
655	ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";	658	ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
Lines 669-675 sub GetSerials { Link Here
669	}	672	}
670		673
671	# OK, now add the last 5 issues arrives/missing	674	# OK, now add the last 5 issues arrives/missing
672	$query = "SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes	675	$query = "SELECT serialid,serialseq, status, planneddate, publisheddate,
		676	publisheddatetext, notes, routingnotes
673	FROM serial	677	FROM serial
674	WHERE subscriptionid = ?	678	WHERE subscriptionid = ?
675	AND (status in (2,4,5))	679	AND (status in (2,4,5))
Lines 711-717 sub GetSerials2 { Link Here
711	my ( $subscription, $status ) = @_;	715	my ( $subscription, $status ) = @_;
712	my $dbh = C4::Context->dbh;	716	my $dbh = C4::Context->dbh;
713	my $query = qq\|	717	my $query = qq\|
714	SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes	718	SELECT serialid,serialseq, status, planneddate, publisheddate,
		719	publisheddatetext, notes, routingnotes
715	FROM serial	720	FROM serial
716	WHERE subscriptionid=$subscription AND status IN ($status)	721	WHERE subscriptionid=$subscription AND status IN ($status)
717	ORDER BY publisheddate,serialid DESC	722	ORDER BY publisheddate,serialid DESC
Lines 1006-1012 sub ModSubscriptionHistory { Link Here
1006		1011
1007	=head2 ModSerialStatus	1012	=head2 ModSerialStatus
1008		1013
1009	ModSerialStatus($serialid,$serialseq, $planneddate,$publisheddate,$status,$notes)	1014	ModSerialStatus($serialid, $serialseq, $planneddate, $publisheddate,
		1015	$publisheddatetext, $status, $notes);
1010		1016
1011	This function modify the serial status. Serial status is a number.(eg 2 is "arrived")	1017	This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
1012	Note : if we change from "waited" to something else,then we will have to create a new "waited" entry	1018	Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
Lines 1014-1020 Note : if we change from "waited" to something else,then we will have to create Link Here
1014	=cut	1020	=cut
1015		1021
1016	sub ModSerialStatus {	1022	sub ModSerialStatus {
1017	my ( $serialid, $serialseq, $planneddate, $publisheddate, $status, $notes ) = @_;	1023	my ($serialid, $serialseq, $planneddate, $publisheddate, $publisheddatetext,
		1024	$status, $notes) = @_;
1018		1025
1019	#It is a usual serial	1026	#It is a usual serial
1020	# 1st, get previous status :	1027	# 1st, get previous status :
Lines 1030-1039 sub ModSerialStatus { Link Here
1030	DelIssue( {'serialid'=>$serialid, 'subscriptionid'=>$subscriptionid,'serialseq'=>$serialseq} );	1037	DelIssue( {'serialid'=>$serialid, 'subscriptionid'=>$subscriptionid,'serialseq'=>$serialseq} );
1031	}	1038	}
1032	else {	1039	else {
1033	my $query =	1040	my $query = '
1034	'UPDATE serial SET serialseq=?,publisheddate=?,planneddate=?,status=?,notes=? WHERE serialid = ?';	1041	UPDATE serial
		1042	SET serialseq = ?, publisheddate = ?, publisheddatetext = ?,
		1043	planneddate = ?, status = ?, notes = ?
		1044	WHERE serialid = ?
		1045	';
1035	$sth = $dbh->prepare($query);	1046	$sth = $dbh->prepare($query);
1036	$sth->execute( $serialseq, $publisheddate, $planneddate, $status, $notes, $serialid );	1047	$sth->execute( $serialseq, $publisheddate, $publisheddatetext,
		1048	$planneddate, $status, $notes, $serialid );
1037	$query = "SELECT * FROM subscription WHERE subscriptionid = ?";	1049	$query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1038	$sth = $dbh->prepare($query);	1050	$sth = $dbh->prepare($query);
1039	$sth->execute($subscriptionid);	1051	$sth->execute($subscriptionid);
Lines 1389-1405 returns the serial id Link Here
1389	=cut	1401	=cut
1390		1402
1391	sub NewIssue {	1403	sub NewIssue {
1392	my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, $publisheddate, $notes ) = @_;	1404	my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate,
		1405	$publisheddate, $publisheddatetext, $notes ) = @_;
1393	### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?	1406	### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
1394		1407
1395	my $dbh = C4::Context->dbh;	1408	my $dbh = C4::Context->dbh;
1396	my $query = qq\|	1409	my $query = qq\|
1397	INSERT INTO serial	1410	INSERT INTO serial (serialseq, subscriptionid, biblionumber, status,
1398	(serialseq,subscriptionid,biblionumber,status,publisheddate,planneddate,notes)	1411	publisheddate, publisheddatetext, planneddate, notes)
1399	VALUES (?,?,?,?,?,?,?)	1412	VALUES (?,?,?,?,?,?,?,?)
1400	\|;	1413	\|;
1401	my $sth = $dbh->prepare($query);	1414	my $sth = $dbh->prepare($query);
1402	$sth->execute( $serialseq, $subscriptionid, $biblionumber, $status, $publisheddate, $planneddate, $notes );	1415	$sth->execute( $serialseq, $subscriptionid, $biblionumber, $status,
		1416	$publisheddate, $publisheddatetext, $planneddate, $notes );
1403	my $serialid = $dbh->{'mysql_insertid'};	1417	my $serialid = $dbh->{'mysql_insertid'};
1404	$query = qq\|	1418	$query = qq\|
1405	SELECT missinglist,recievedlist	1419	SELECT missinglist,recievedlist

Lines 5452-5457 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (+12 lines)
5452	SetVersion($DBversion);	5452	SetVersion($DBversion);
5453	}	5453	}
5454		5454
		5455	$DBversion = "XXX";
		5456	if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
		5457	$dbh->do("
		5458	ALTER TABLE serial
		5459	ADD COLUMN publisheddatetext VARCHAR(100) DEFAULT NULL
		5460	AFTER publisheddate
		5461	");
		5462
		5463	print "Upgrade to $DBversion done (Add serial.publisheddatetext)\n";
		5464	SetVersion($DBversion);
		5465	}
		5466
5455	=head1 FUNCTIONS	5467	=head1 FUNCTIONS
5456		5468
5457	=head2 TableExists($table)	5469	=head2 TableExists($table)

Lines 57-71 if($op eq 'gennext' && @subscriptionid){ Link Here

(-)a/serials/serials-collection.pl (-4 / +8 lines)
57	my $subscriptionid = $subscriptionid[0];	57	my $subscriptionid = $subscriptionid[0];
58	my $subscription = GetSubscription($subscriptionid);	58	my $subscription = GetSubscription($subscriptionid);
59		59
60	my $sth = $dbh->prepare("SELECT publisheddate, serialid, serialseq, planneddate	60	my $sth = $dbh->prepare("
61	FROM serial WHERE status = 1 AND subscriptionid = ?");	61	SELECT publisheddate, publisheddatetext, serialid, serialseq,
		62	planneddate
		63	FROM serial
		64	WHERE status = 1 AND subscriptionid = ?
		65	");
62	$sth->execute($subscriptionid);	66	$sth->execute($subscriptionid);
63		67
64	# modify actual expected issue, to generate the next	68	# modify actual expected issue, to generate the next
65	if ( my $issue = $sth->fetchrow_hashref ) {	69	if ( my $issue = $sth->fetchrow_hashref ) {
66	ModSerialStatus( $issue->{serialid}, $issue->{serialseq},	70	ModSerialStatus( $issue->{serialid}, $issue->{serialseq},
67	$issue->{planneddate}, $issue->{publisheddate},	71	$issue->{planneddate}, $issue->{publisheddate},
68	3, "" );	72	$issue->{publisheddatetext}, 3, "" );
69	}else{	73	}else{
70	my $expected = GetNextExpected($subscriptionid);	74	my $expected = GetNextExpected($subscriptionid);
71	my (	75	my (