Attachment #9976 for bug #5342

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

Lines 88-97 sub GetSuppliersWithLateIssues { Link Here

(-)a/C4/Serials.pm (-9 / +17 lines)
88	my $dbh = C4::Context->dbh;	88	my $dbh = C4::Context->dbh;
89	my $query = qq\|	89	my $query = qq\|
90	SELECT DISTINCT id, name	90	SELECT DISTINCT id, name
91	FROM subscription	91	FROM subscription
92	LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid	92	LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid
93	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id	93	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
94	WHERE id > 0 AND ((planneddate < now() AND serial.status=1) OR serial.STATUS = 3 OR serial.STATUS = 4) ORDER BY name\|;	94	WHERE id > 0 AND ((planneddate < now() AND serial.status=1) OR serial.STATUS = 3 OR serial.STATUS = 4 OR serial.STATUS = 7)
		95	ORDER BY name
		96	\|;
95	return $dbh->selectall_arrayref($query, { Slice => {} });	97	return $dbh->selectall_arrayref($query, { Slice => {} });
96	}	98	}
97		99
Lines 284-291 sub UpdateClaimdateIssues { Link Here
284	my $dbh = C4::Context->dbh;	286	my $dbh = C4::Context->dbh;
285	$date = strftime( "%Y-%m-%d", localtime ) unless ($date);	287	$date = strftime( "%Y-%m-%d", localtime ) unless ($date);
286	my $query = "	288	my $query = "
287	UPDATE serial SET claimdate = ?, status = 7	289	UPDATE serial
288	WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")";	290	SET claimdate = ?,
		291	status = 7,
		292	claims_count = claims_count + 1
		293	WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")
		294	";
289	my $rq = $dbh->prepare($query);	295	my $rq = $dbh->prepare($query);
290	$rq->execute($date, @$serialids);	296	$rq->execute($date, @$serialids);
291	return $rq->rows;	297	return $rq->rows;
Lines 1756-1762 sub GetLateOrMissingIssues { Link Here
1756	"SELECT	1762	"SELECT
1757	serialid, aqbooksellerid, name,	1763	serialid, aqbooksellerid, name,
1758	biblio.title, planneddate, serialseq,	1764	biblio.title, planneddate, serialseq,
1759	serial.status, serial.subscriptionid, claimdate,	1765	serial.status, serial.subscriptionid, claimdate, claims_count,
1760	subscription.branchcode	1766	subscription.branchcode
1761	FROM serial	1767	FROM serial
1762	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid	1768	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
Lines 1773-1779 sub GetLateOrMissingIssues { Link Here
1773	"SELECT	1779	"SELECT
1774	serialid, aqbooksellerid, name,	1780	serialid, aqbooksellerid, name,
1775	biblio.title, planneddate, serialseq,	1781	biblio.title, planneddate, serialseq,
1776	serial.status, serial.subscriptionid, claimdate,	1782	serial.status, serial.subscriptionid, claimdate, claims_count,
1777	subscription.branchcode	1783	subscription.branchcode
1778	FROM serial	1784	FROM serial
1779	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid	1785	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
Lines 1853-1859 sub updateClaim { Link Here
1853	my ($serialid) = @_;	1859	my ($serialid) = @_;
1854	my $dbh = C4::Context->dbh;	1860	my $dbh = C4::Context->dbh;
1855	my $sth = $dbh->prepare(	1861	my $sth = $dbh->prepare(
1856	"UPDATE serial SET claimdate = now()	1862	"UPDATE serial
		1863	SET claimdate = now(),
		1864	claims_count = claims_count + 1
1857	WHERE serialid = ?	1865	WHERE serialid = ?
1858	"	1866	"
1859	);	1867	);

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

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
1768	`publisheddate` date default NULL,	1768	`publisheddate` date default NULL,
1769	`itemnumber` text default NULL,	1769	`itemnumber` text default NULL,
1770	`claimdate` date default NULL,	1770	`claimdate` date default NULL,
		1771	claims_count int(11) default 0,
1771	`routingnotes` text,	1772	`routingnotes` text,
1772	PRIMARY KEY (`serialid`)	1773	PRIMARY KEY (`serialid`)
1773	) ENGINE=InnoDB DEFAULT CHARSET=utf8;	1774	) ENGINE=InnoDB DEFAULT CHARSET=utf8;




    SetVersion ($DBversion);
}




$DBversion = "3.07.00.XXX";
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
    $dbh->do("ALTER TABLE serial ADD COLUMN claims_count INT(11) DEFAULT 0");
    print "Upgrade to $DBversion done (Add claims_count field in serial table)\n";
    SetVersion($DBversion);
}

=head1 FUNCTIONS

=head2 TableExists($table)

Lines 164-169 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt (-45 / +51 lines)
164		164
165	<h1>Claims</h1>	165	<h1>Claims</h1>
166		166
		167	[% IF error_claim %]
		168	[% IF ( error_claim == "no_email" ) %]
		169	<div class="error">This vendor has no email</div>
		170	[% ELSE %]
		171	<div class="error">[% error_claim %]</div>
		172	[% END %]
		173	[% END %]
		174	[% IF info_claim %]
		175	<div class="dialog message">Email has been sent.</div>
		176	[% END %]
		177
167	[% IF ( letter ) %][% UNLESS ( missingissues ) %][% IF ( supplierid ) %] <div class="dialog alert">No missing issues found.</div>[% ELSE %]<div class="dialog message">Please choose a vendor.</div>[% END %][% END %][% END %]	178	[% IF ( letter ) %][% UNLESS ( missingissues ) %][% IF ( supplierid ) %] <div class="dialog alert">No missing issues found.</div>[% ELSE %]<div class="dialog message">Please choose a vendor.</div>[% END %][% END %][% END %]
168		179
169	[% IF ( SHOWCONFIRMATION ) %]	180	[% IF ( SHOWCONFIRMATION ) %]
Lines 214-219 Link Here
214	<li>	225	<li>
215	<label for="titlefilter">Title : </label>	226	<label for="titlefilter">Title : </label>
216	<input id="titlefilter" type="text" />	227	<input id="titlefilter" type="text" />
		228	</li>
		229	<li>
217	<label for="branchfilter">Library: </label>	230	<label for="branchfilter">Library: </label>
218	<select id="branchfilter" onchange="filterByBranch();">	231	<select id="branchfilter" onchange="filterByBranch();">
219	[% FOREACH branchloo IN branchloop %]	232	[% FOREACH branchloo IN branchloop %]
Lines 263-303 Link Here
263	</form>	276	</form>
264		277
265	<fieldset>	278	<fieldset>
266	<form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">	279	<form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">
267	<input type="hidden" name="order" value="[% order %]" />	280	<table id="claimst">
268	<table id="claimst">	281	<thead>
269	<thead><tr>	282	<tr>
270	[% IF ( letter ) %]	283	[% IF ( letter ) %]
271	<th><input type="checkbox" id="CheckAll"></th>	284	<th><input type="checkbox" id="CheckAll"></th>
272	[% END %]	285	[% END %]
273	<th>Vendor</th>	286	<th>Vendor</th>
274	<th>Library</th>	287	<th>Library</th>
275	<th>Title</th>	288	<th>Title</th>
276	<th>Issue number</th>	289	<th>Issue number</th>
277	<th>Status</th>	290	<th>Status</th>
278	<th>Since</th>	291	<th>Since</th>
279	<th>Claim date</th>	292	<th>Claims count</th>
280	<th>Begin claim</th>	293	<th>Claim date</th>
281	</tr></thead>	294	</tr>
		295	</thead>
282	<tbody>[% FOREACH missingissue IN missingissues %]	296	<tbody>[% FOREACH missingissue IN missingissues %]
283	<tr>	297	<tr>
284	[% IF ( letter ) %]	298	[% IF ( letter ) %]
285	<td>	299	<td>
286	<input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />	300	<input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />
287	</td>	301	</td>
288	[% END %]	302	[% END %]
289	<td>	303	<td>[% missingissue.name %]</td>
290	[% missingissue.name %]	304	<td>[% missingissue.branchcode %]</td>
291	</td>
292	<td>
293	[% missingissue.branchcode %]
294	</td>
295	<td>	305	<td>
296	<a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% missingissue.subscriptionid %]">[% missingissue.title \|html %]</a>	306	<a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% missingissue.subscriptionid %]">[% missingissue.title \|html %]</a>
297	</td>	307	</td>
298	<td>	308	<td>[% missingissue.serialseq %]</td>
299	[% missingissue.serialseq %]
300	</td>
301	<td>	309	<td>
302	[% IF ( missingissue.status1 ) %]Expected[% END %]	310	[% IF ( missingissue.status1 ) %]Expected[% END %]
303	[% IF ( missingissue.status2 ) %]Arrived[% END %]	311	[% IF ( missingissue.status2 ) %]Arrived[% END %]
Lines 305-332 Link Here
305	[% IF ( missingissue.status4 ) %]Missing[% END %]	313	[% IF ( missingissue.status4 ) %]Missing[% END %]
306	[% IF ( missingissue.status7 ) %]Claimed[% END %]	314	[% IF ( missingissue.status7 ) %]Claimed[% END %]
307	</td>	315	</td>
308	<td class="planneddate">	316	<td class="planneddate">[% missingissue.planneddate %]</td>
309	[% missingissue.planneddate %]	317	<td>[% missingissue.claims_count %]</td>
310	</td>	318	<td>[% missingissue.claimdate %]</td>
311	<td>
312	[% missingissue.claimdate %]
313	</td>
314	<td>
315	<a href="/cgi-bin/koha/serials/lateissues-excel.pl?supplierid=[% missingissue.supplieri %]&serialid=[% missingissue.serialid %]&op=claims">Export item data</a>
316	</td>
317	</tr>	319	</tr>
318	[% END %]</tbody>	320	[% END %]</tbody>
319	</table>	321	</table>
320	<p><span class="exportSelected"></span></p>	322	<p><span class="exportSelected"></span></p>
321		323
322	[% IF ( letter ) %]	324	[% IF ( letter ) %]
323	<fieldset class="action"> <label for="letter_code">Select notice:</label>	325	<fieldset class="action">
324	<select name="letter_code" id="letter_code">	326	<label for="letter_code">Select notice:</label>
325	[% FOREACH letter IN letters %]	327	<select name="letter_code" id="letter_code">
326	<option value="[% letter.code %]">[% letter.name %]</option>	328	[% FOREACH letter IN letters %]
327	[% END %]	329	<option value="[% letter.code %]">[% letter.name %]</option>
328	</select>	330	[% END %]
329	<input type="hidden" name="op" value="send_alert" /><input type="submit" name="submit" class="button" value="Send notification" /></fieldset>	331	</select>
		332	<input type="hidden" name="op" value="send_alert" />
		333	<input type="hidden" name="supplierid" value="[% supplierid %]" />
		334	<input type="submit" name="submit" class="button" value="Send notification" />
		335	</fieldset>
330	[% END %]	336	[% END %]
331	</form>	337	</form>
332	</fieldset>	338	</fieldset>




my $claimletter = $input->param('claimletter');
my $supplierid = $input->param('supplierid');
my $suppliername = $input->param('suppliername');
my $order = $input->param('order');

# open template first (security & userenv set here)
my ($template, $loggedinuser, $cookie)

# supplierlist is returned in name order
my $supplierlist = GetSuppliersWithLateIssues();
for my $s (@{$supplierlist} ) {
    $s->{count} = scalar  GetLateOrMissingIssues($s->{id}, q{});
    if ($supplierid && $s->{id} == $supplierid) {
        $s->{selected} = 1;
    }
}

my $branchloop = GetBranchesLoop();
unshift @$branchloop, {value=> 'all',name=>''};

my $preview=0;
if($op && $op eq 'preview'){
    $preview = 1;
} else {
    my @serialnums=$input->param('serialid');
    if (@serialnums) { # i.e. they have been flagged to generate claims
        my $err;
        eval {
            $err = SendAlerts('claimissues',\@serialnums,$input->param("letter_code"));
            if ( not ref $err or not exists $err->{error} ) {
               UpdateClaimdateIssues(\@serialnums);
            }
        };
        if ( $@ ) {
            $template->param(error_claim => $@);
        } elsif ( ref $err and exists $err->{error} and $err->{error} eq "no_email" ) {
            $template->{VARS}->{'error_claim'} = "no_email";
        } else {
            $template->{VARS}->{'info_claim'} = 1;
        }
    }
}

my $letters = GetLetters('claimissues');
my @letters;
foreach (keys %{$letters}){

my  @missingissues;
my @supplierinfo;
if ($supplierid) {
    @missingissues = GetLateOrMissingIssues($supplierid);
    @supplierinfo=GetBookSeller($supplierid);
}

my $branchloop = GetBranchesLoop();
unshift @$branchloop, {value=> 'all',name=>''};

my $preview=0;
if($op && $op eq 'preview'){
    $preview = 1;
} else {
    my @serialnums=$input->param('serialid');
    if (@serialnums) { # i.e. they have been flagged to generate claims
        SendAlerts('claimissues',\@serialnums,$input->param("letter_code"));
        my $cntupdate=UpdateClaimdateIssues(\@serialnums);
        ### $cntupdate SHOULD be equal to scalar(@$serialnums)
    }
}
$template->param('letters'=>\@letters,'letter'=>$letter);
$template->param(
        order =>$order,
        suploop => $supplierlist,
        phone => $supplierinfo[0]->{phone},
        booksellerfax => $supplierinfo[0]->{booksellerfax},
- 

Return to bug 5342

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

(-)a/installer/data/mysql/updatedatabase.pl (+10 lines)
5308	SetVersion ($DBversion);	5308	SetVersion ($DBversion);
5309	}	5309	}
5310		5310
		5311
		5312
		5313
		5314	$DBversion = "3.07.00.XXX";
		5315	if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
		5316	$dbh->do("ALTER TABLE serial ADD COLUMN claims_count INT(11) DEFAULT 0");
		5317	print "Upgrade to $DBversion done (Add claims_count field in serial table)\n";
		5318	SetVersion($DBversion);
		5319	}
		5320
5311	=head1 FUNCTIONS	5321	=head1 FUNCTIONS
5312		5322
5313	=head2 TableExists($table)	5323	=head2 TableExists($table)

Lines 36-42 my $op = $input->param('op'); Link Here

(-)a/serials/claims.pl (-19 / +28 lines)
36	my $claimletter = $input->param('claimletter');	36	my $claimletter = $input->param('claimletter');
37	my $supplierid = $input->param('supplierid');	37	my $supplierid = $input->param('supplierid');
38	my $suppliername = $input->param('suppliername');	38	my $suppliername = $input->param('suppliername');
39	my $order = $input->param('order');
40		39
41	# open template first (security & userenv set here)	40	# open template first (security & userenv set here)
42	my ($template, $loggedinuser, $cookie)	41	my ($template, $loggedinuser, $cookie)
Lines 51-62 my ($template, $loggedinuser, $cookie) Link Here
51	# supplierlist is returned in name order	50	# supplierlist is returned in name order
52	my $supplierlist = GetSuppliersWithLateIssues();	51	my $supplierlist = GetSuppliersWithLateIssues();
53	for my $s (@{$supplierlist} ) {	52	for my $s (@{$supplierlist} ) {
54	$s->{count} = scalar GetLateOrMissingIssues($s->{id}, q{}, $order);	53	$s->{count} = scalar GetLateOrMissingIssues($s->{id}, q{});
55	if ($supplierid && $s->{id} == $supplierid) {	54	if ($supplierid && $s->{id} == $supplierid) {
56	$s->{selected} = 1;	55	$s->{selected} = 1;
57	}	56	}
58	}	57	}
59		58
		59	my $branchloop = GetBranchesLoop();
		60	unshift @$branchloop, {value=> 'all',name=>''};
		61
		62	my $preview=0;
		63	if($op && $op eq 'preview'){
		64	$preview = 1;
		65	} else {
		66	my @serialnums=$input->param('serialid');
		67	if (@serialnums) { # i.e. they have been flagged to generate claims
		68	my $err;
		69	eval {
		70	$err = SendAlerts('claimissues',\@serialnums,$input->param("letter_code"));
		71	if ( not ref $err or not exists $err->{error} ) {
		72	UpdateClaimdateIssues(\@serialnums);
		73	}
		74	};
		75	if ( $@ ) {
		76	$template->param(error_claim => $@);
		77	} elsif ( ref $err and exists $err->{error} and $err->{error} eq "no_email" ) {
		78	$template->{VARS}->{'error_claim'} = "no_email";
		79	} else {
		80	$template->{VARS}->{'info_claim'} = 1;
		81	}
		82	}
		83	}
		84
60	my $letters = GetLetters('claimissues');	85	my $letters = GetLetters('claimissues');
61	my @letters;	86	my @letters;
62	foreach (keys %{$letters}){	87	foreach (keys %{$letters}){
Lines 67-93 my $letter=((scalar(@letters)>1) \|\| ($letters[0]->{name}\|\|$letters[0]->{code})); Link Here
67	my @missingissues;	92	my @missingissues;
68	my @supplierinfo;	93	my @supplierinfo;
69	if ($supplierid) {	94	if ($supplierid) {
70	@missingissues = GetLateOrMissingIssues($supplierid,$serialid,$order);	95	@missingissues = GetLateOrMissingIssues($supplierid);
71	@supplierinfo=GetBookSeller($supplierid);	96	@supplierinfo=GetBookSeller($supplierid);
72	}	97	}
73		98
74	my $branchloop = GetBranchesLoop();
75	unshift @$branchloop, {value=> 'all',name=>''};
76
77	my $preview=0;
78	if($op && $op eq 'preview'){
79	$preview = 1;
80	} else {
81	my @serialnums=$input->param('serialid');
82	if (@serialnums) { # i.e. they have been flagged to generate claims
83	SendAlerts('claimissues',\@serialnums,$input->param("letter_code"));
84	my $cntupdate=UpdateClaimdateIssues(\@serialnums);
85	### $cntupdate SHOULD be equal to scalar(@$serialnums)
86	}
87	}
88	$template->param('letters'=>\@letters,'letter'=>$letter);	99	$template->param('letters'=>\@letters,'letter'=>$letter);
89	$template->param(	100	$template->param(
90	order =>$order,
91	suploop => $supplierlist,	101	suploop => $supplierlist,
92	phone => $supplierinfo[0]->{phone},	102	phone => $supplierinfo[0]->{phone},
93	booksellerfax => $supplierinfo[0]->{booksellerfax},	103	booksellerfax => $supplierinfo[0]->{booksellerfax},
94	-