Attachment #16217 for bug #5342

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




sub GetSuppliersWithLateIssues {
    my $dbh   = C4::Context->dbh;
    my $query = qq|
    SELECT DISTINCT id, name
    FROM            subscription
    LEFT JOIN       serial ON serial.subscriptionid=subscription.subscriptionid
    LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
    WHERE id > 0
        AND (
            (planneddate < now() AND serial.status=1)
            OR serial.STATUS = 3 OR serial.STATUS = 4 OR serial.STATUS = 7
        )
        AND subscription.closed = 0
    ORDER BY name|;

    my $dbh = C4::Context->dbh;
    $date = strftime( "%Y-%m-%d", localtime ) unless ($date);
    my $query = "
        UPDATE serial
        SET claimdate = ?,
            status = 7,
            claims_count = claims_count + 1
        WHERE  serialid in (" . join( ",", map { '?' } @$serialids ) . ")
    ";
    my $rq = $dbh->prepare($query);
    $rq->execute($date, @$serialids);
    return $rq->rows;

            "SELECT
                serialid,      aqbooksellerid,        name,
                biblio.title,  planneddate,           serialseq,
                serial.status, serial.subscriptionid, claimdate, claims_count,
                subscription.branchcode
            FROM      serial 
                LEFT JOIN subscription  ON serial.subscriptionid=subscription.subscriptionid 

            "SELECT 
            serialid,      aqbooksellerid,         name,
            biblio.title,  planneddate,           serialseq,
                serial.status, serial.subscriptionid, claimdate, claims_count,
                subscription.branchcode
            FROM serial 
                LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid 

    my ($serialid) = @_;
    my $dbh        = C4::Context->dbh;
    my $sth        = $dbh->prepare(
        "UPDATE serial
         SET claimdate = now(),
             claims_count = claims_count + 1
                WHERE serialid = ?
        "
    );

Lines 1802-1807 CREATE TABLE `serial` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
1802	`publisheddate` date default NULL,	1802	`publisheddate` date default NULL,
1803	`itemnumber` text default NULL,	1803	`itemnumber` text default NULL,
1804	`claimdate` date default NULL,	1804	`claimdate` date default NULL,
		1805	claims_count int(11) default 0,
1805	`routingnotes` text,	1806	`routingnotes` text,
1806	PRIMARY KEY (`serialid`)	1807	PRIMARY KEY (`serialid`)
1807	) ENGINE=InnoDB DEFAULT CHARSET=utf8;	1808	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

(-)a/installer/data/mysql/updatedatabase.pl (+8 lines)
6532	SetVersion ($DBversion);	6532	SetVersion ($DBversion);
6533	}	6533	}
6534		6534
		6535
		6536	$DBversion = "3.11.00.XXX";
		6537	if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
		6538	$dbh->do("ALTER TABLE serial ADD COLUMN claims_count INT(11) DEFAULT 0 after claimdate");
		6539	print "Upgrade to $DBversion done (Add claims_count field in serial table)\n";
		6540	SetVersion($DBversion);
		6541	}
		6542
6535	=head1 FUNCTIONS	6543	=head1 FUNCTIONS
6536		6544
6537	=head2 TableExists($table)	6545	=head2 TableExists($table)





    <h1>Claims</h1>

    [% IF error_claim %]
        [% IF ( error_claim == "no_email" ) %]
            <div class="error">This vendor has no email</div>
        [% ELSE %]
            <div class="error">[% error_claim %]</div>
        [% END %]
    [% END %]
    [% IF info_claim %]
        <div class="dialog message">Email has been sent.</div>
    [% END %]

[% 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 %]
	
	     [% IF ( SHOWCONFIRMATION ) %]

    </form>

    <fieldset>
        <form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">
            <table id="claimst">
                <thead>
                    <tr>
                        [% IF ( letter ) %]
                            <th><input type="checkbox" id="CheckAll"></th>
                        [% END %]
                        <th>Vendor</th>
                        <th>Library</th>
                        <th>Title</th>
                        <th>Issue number</th>
                        <th>Status</th>
                        <th>Since</th>
                        <th>Claims count</th>
                        <th>Claim date</th>
                    </tr>
                </thead>
                <tbody>[% FOREACH missingissue IN missingissues %]
                    <tr>
                        [% IF ( letter ) %]
                            <td>
                                <input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />
                            </td>
                        [% END %]
                        <td>[% missingissue.name %]</td>
                        <td>[% missingissue.branchcode %]</td>
                        </td>
                        <td>
                        [% missingissue.branchcode %]
                        </td>
                        <td>
                        <a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% missingissue.subscriptionid %]">[% missingissue.title |html %]</a>
                        </td>
                        <td>[% missingissue.serialseq %]</td>
                        [% missingissue.serialseq %]
                        </td>
                        <td>
                            [% IF ( missingissue.status1 ) %]Expected[% END %]
                            [% IF ( missingissue.status2 ) %]Arrived[% END %]

                            [% IF ( missingissue.status7 ) %]Claimed[% END %]
                            [% IF ( missingissue.status8 ) %]Stopped[% END %]
                        </td>
                        <td class="planneddate">[% missingissue.planneddate %]</td>
                        <td>[% missingissue.claims_count %]</td>
                        <td>[% missingissue.claimdate %]</td>
                        <td>
                        [% missingissue.claimdate %]
                        </td>
                        <td>
                            <a href="/cgi-bin/koha/serials/lateissues-excel.pl?supplierid=[% missingissue.supplieri %]&amp;serialid=[% missingissue.serialid %]&amp;op=claims">Export item data</a>
                        </td>
                    </tr>
                [% END %]</tbody>
            </table>
            <p><span class="exportSelected"></span></p>

            [% IF ( letter ) %]
                <fieldset class="action">
                    <label for="letter_code">Select notice:</label>
                    <select name="letter_code" id="letter_code">
                        [% FOREACH letter IN letters %]
                            <option value="[% letter.code %]">[% letter.name %]</option>
                        [% END %]
                    </select>
                    <input type="hidden" name="op" value="send_alert" />
                    <input type="hidden" name="supplierid" value="[% supplierid %]" />
                    <input type="submit" name="submit" class="button" value="Send notification" />
                </fieldset>
            [% END %]
        </form>
    </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 89-102 the array is in name order Link Here

(-)a/C4/Serials.pm (-7 / +13 lines)
89	sub GetSuppliersWithLateIssues {	89	sub GetSuppliersWithLateIssues {
90	my $dbh = C4::Context->dbh;	90	my $dbh = C4::Context->dbh;
91	my $query = qq\|	91	my $query = qq\|
92	SELECT DISTINCT id, name	92	SELECT DISTINCT id, name
93	FROM subscription	93	FROM subscription
94	LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid	94	LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid
95	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id	95	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
96	WHERE id > 0	96	WHERE id > 0
97	AND (	97	AND (
98	(planneddate < now() AND serial.status=1)	98	(planneddate < now() AND serial.status=1)
99	OR serial.STATUS = 3 OR serial.STATUS = 4	99	OR serial.STATUS = 3 OR serial.STATUS = 4 OR serial.STATUS = 7
100	)	100	)
101	AND subscription.closed = 0	101	AND subscription.closed = 0
102	ORDER BY name\|;	102	ORDER BY name\|;
Lines 294-301 sub UpdateClaimdateIssues { Link Here
294	my $dbh = C4::Context->dbh;	294	my $dbh = C4::Context->dbh;
295	$date = strftime( "%Y-%m-%d", localtime ) unless ($date);	295	$date = strftime( "%Y-%m-%d", localtime ) unless ($date);
296	my $query = "	296	my $query = "
297	UPDATE serial SET claimdate = ?, status = 7	297	UPDATE serial
298	WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")";	298	SET claimdate = ?,
		299	status = 7,
		300	claims_count = claims_count + 1
		301	WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")
		302	";
299	my $rq = $dbh->prepare($query);	303	my $rq = $dbh->prepare($query);
300	$rq->execute($date, @$serialids);	304	$rq->execute($date, @$serialids);
301	return $rq->rows;	305	return $rq->rows;
Lines 1859-1865 sub GetLateOrMissingIssues { Link Here
1859	"SELECT	1863	"SELECT
1860	serialid, aqbooksellerid, name,	1864	serialid, aqbooksellerid, name,
1861	biblio.title, planneddate, serialseq,	1865	biblio.title, planneddate, serialseq,
1862	serial.status, serial.subscriptionid, claimdate,	1866	serial.status, serial.subscriptionid, claimdate, claims_count,
1863	subscription.branchcode	1867	subscription.branchcode
1864	FROM serial	1868	FROM serial
1865	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid	1869	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
Lines 1876-1882 sub GetLateOrMissingIssues { Link Here
1876	"SELECT	1880	"SELECT
1877	serialid, aqbooksellerid, name,	1881	serialid, aqbooksellerid, name,
1878	biblio.title, planneddate, serialseq,	1882	biblio.title, planneddate, serialseq,
1879	serial.status, serial.subscriptionid, claimdate,	1883	serial.status, serial.subscriptionid, claimdate, claims_count,
1880	subscription.branchcode	1884	subscription.branchcode
1881	FROM serial	1885	FROM serial
1882	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid	1886	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
Lines 1956-1962 sub updateClaim { Link Here
1956	my ($serialid) = @_;	1960	my ($serialid) = @_;
1957	my $dbh = C4::Context->dbh;	1961	my $dbh = C4::Context->dbh;
1958	my $sth = $dbh->prepare(	1962	my $sth = $dbh->prepare(
1959	"UPDATE serial SET claimdate = now()	1963	"UPDATE serial
		1964	SET claimdate = now(),
		1965	claims_count = claims_count + 1
1960	WHERE serialid = ?	1966	WHERE serialid = ?
1961	"	1967	"
1962	);	1968	);

Lines 164-169 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt (-44 / +48 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 246-286 Link Here
246	</form>	257	</form>
247		258
248	<fieldset>	259	<fieldset>
249	<form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">	260	<form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">
250	<input type="hidden" name="order" value="[% order %]" />	261	<table id="claimst">
251	<table id="claimst">	262	<thead>
252	<thead><tr>	263	<tr>
253	[% IF ( letter ) %]	264	[% IF ( letter ) %]
254	<th><input type="checkbox" id="CheckAll"></th>	265	<th><input type="checkbox" id="CheckAll"></th>
255	[% END %]	266	[% END %]
256	<th>Vendor</th>	267	<th>Vendor</th>
257	<th>Library</th>	268	<th>Library</th>
258	<th>Title</th>	269	<th>Title</th>
259	<th>Issue number</th>	270	<th>Issue number</th>
260	<th>Status</th>	271	<th>Status</th>
261	<th>Since</th>	272	<th>Since</th>
262	<th>Claim date</th>	273	<th>Claims count</th>
263	<th>Begin claim</th>	274	<th>Claim date</th>
264	</tr></thead>	275	</tr>
		276	</thead>
265	<tbody>[% FOREACH missingissue IN missingissues %]	277	<tbody>[% FOREACH missingissue IN missingissues %]
266	<tr>	278	<tr>
267	[% IF ( letter ) %]	279	[% IF ( letter ) %]
268	<td>	280	<td>
269	<input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />	281	<input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />
270	</td>	282	</td>
271	[% END %]	283	[% END %]
272	<td>	284	<td>[% missingissue.name %]</td>
273	[% missingissue.name %]	285	<td>[% missingissue.branchcode %]</td>
274	</td>
275	<td>
276	[% missingissue.branchcode %]
277	</td>
278	<td>	286	<td>
279	<a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% missingissue.subscriptionid %]">[% missingissue.title \|html %]</a>	287	<a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% missingissue.subscriptionid %]">[% missingissue.title \|html %]</a>
280	</td>	288	</td>
281	<td>	289	<td>[% missingissue.serialseq %]</td>
282	[% missingissue.serialseq %]
283	</td>
284	<td>	290	<td>
285	[% IF ( missingissue.status1 ) %]Expected[% END %]	291	[% IF ( missingissue.status1 ) %]Expected[% END %]
286	[% IF ( missingissue.status2 ) %]Arrived[% END %]	292	[% IF ( missingissue.status2 ) %]Arrived[% END %]
Lines 289-316 Link Here
289	[% IF ( missingissue.status7 ) %]Claimed[% END %]	295	[% IF ( missingissue.status7 ) %]Claimed[% END %]
290	[% IF ( missingissue.status8 ) %]Stopped[% END %]	296	[% IF ( missingissue.status8 ) %]Stopped[% END %]
291	</td>	297	</td>
292	<td class="planneddate">	298	<td class="planneddate">[% missingissue.planneddate %]</td>
293	[% missingissue.planneddate %]	299	<td>[% missingissue.claims_count %]</td>
294	</td>	300	<td>[% missingissue.claimdate %]</td>
295	<td>
296	[% missingissue.claimdate %]
297	</td>
298	<td>
299	<a href="/cgi-bin/koha/serials/lateissues-excel.pl?supplierid=[% missingissue.supplieri %]&serialid=[% missingissue.serialid %]&op=claims">Export item data</a>
300	</td>
301	</tr>	301	</tr>
302	[% END %]</tbody>	302	[% END %]</tbody>
303	</table>	303	</table>
304	<p><span class="exportSelected"></span></p>	304	<p><span class="exportSelected"></span></p>
305		305
306	[% IF ( letter ) %]	306	[% IF ( letter ) %]
307	<fieldset class="action"> <label for="letter_code">Select notice:</label>	307	<fieldset class="action">
308	<select name="letter_code" id="letter_code">	308	<label for="letter_code">Select notice:</label>
309	[% FOREACH letter IN letters %]	309	<select name="letter_code" id="letter_code">
310	<option value="[% letter.code %]">[% letter.name %]</option>	310	[% FOREACH letter IN letters %]
311	[% END %]	311	<option value="[% letter.code %]">[% letter.name %]</option>
312	</select>	312	[% END %]
313	<input type="hidden" name="op" value="send_alert" /><input type="submit" name="submit" class="button" value="Send notification" /></fieldset>	313	</select>
		314	<input type="hidden" name="op" value="send_alert" />
		315	<input type="hidden" name="supplierid" value="[% supplierid %]" />
		316	<input type="submit" name="submit" class="button" value="Send notification" />
		317	</fieldset>
314	[% END %]	318	[% END %]
315	</form>	319	</form>
316	</fieldset>	320	</fieldset>

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	-