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

(-)a/C4/Overdues.pm (-362 lines)
Lines 41-60 BEGIN { Link Here
41
        &CalcFine
41
        &CalcFine
42
        &Getoverdues
42
        &Getoverdues
43
        &checkoverdues
43
        &checkoverdues
44
        &CheckAccountLineLevelInfo
45
        &CheckAccountLineItemInfo
46
        &CheckExistantNotifyid
47
        &GetNextIdNotify
48
        &GetNotifyId
49
        &NumberNotifyId
44
        &NumberNotifyId
50
        &AmountNotify
45
        &AmountNotify
51
        &UpdateAccountLines
52
        &UpdateFine
46
        &UpdateFine
53
        &GetOverdueDelays
54
        &GetOverduerules
55
        &GetFine
47
        &GetFine
56
        &CreateItemAccountLine
57
        &ReplacementCost2
58
        
48
        
59
        &CheckItemNotify
49
        &CheckItemNotify
60
        &GetOverduesForBranch
50
        &GetOverduesForBranch
Lines 79-85 BEGIN { Link Here
79
	# subs to move to Biblio.pm
69
	# subs to move to Biblio.pm
80
	push @EXPORT, qw(
70
	push @EXPORT, qw(
81
        &GetItems
71
        &GetItems
82
        &ReplacementCost
83
	);
72
	);
84
}
73
}
85
74
Lines 630-636 category he or she belongs to. Link Here
630
619
631
=cut
620
=cut
632
621
633
#'
634
sub BorType {
622
sub BorType {
635
    my ($borrowernumber) = @_;
623
    my ($borrowernumber) = @_;
636
    my $dbh              = C4::Context->dbh;
624
    my $dbh              = C4::Context->dbh;
Lines 643-669 sub BorType { Link Here
643
    return $sth->fetchrow_hashref;
631
    return $sth->fetchrow_hashref;
644
}
632
}
645
633
646
=head2 ReplacementCost
647
648
    $cost = &ReplacementCost($itemnumber);
649
650
Returns the replacement cost of the item with the given item number.
651
652
=cut
653
654
#'
655
sub ReplacementCost {
656
    my ($itemnum) = @_;
657
    my $dbh       = C4::Context->dbh;
658
    my $sth       =
659
      $dbh->prepare("Select replacementprice from items where itemnumber=?");
660
    $sth->execute($itemnum);
661
662
    # FIXME - Use fetchrow_array or a slice.
663
    my $data = $sth->fetchrow_hashref;
664
    return ( $data->{'replacementprice'} );
665
}
666
667
=head2 GetFine
634
=head2 GetFine
668
635
669
    $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
636
    $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
Lines 676-682 C<$borrowernumber> is the borrowernumber Link Here
676
643
677
=cut 
644
=cut 
678
645
679
680
sub GetFine {
646
sub GetFine {
681
    my ( $itemnum, $borrowernumber ) = @_;
647
    my ( $itemnum, $borrowernumber ) = @_;
682
    my $dbh   = C4::Context->dbh();
648
    my $dbh   = C4::Context->dbh();
Lines 692-752 sub GetFine { Link Here
692
    return 0;
658
    return 0;
693
}
659
}
694
660
695
sub ReplacementCost2 {
696
    my ( $itemnum, $borrowernumber ) = @_;
697
    my $dbh   = C4::Context->dbh();
698
    my $query = "SELECT amountoutstanding
699
         FROM accountlines
700
             WHERE accounttype like 'L'
701
         AND amountoutstanding > 0
702
         AND itemnumber = ?
703
         AND borrowernumber= ?";
704
    my $sth = $dbh->prepare($query);
705
    $sth->execute( $itemnum, $borrowernumber );
706
    my $data = $sth->fetchrow_hashref();
707
    return ( $data->{'amountoutstanding'} );
708
}
709
710
711
=head2 GetNextIdNotify
712
713
    ($result) = &GetNextIdNotify($reference);
714
715
Returns the new file number
716
717
C<$result> contains the next file number
718
719
C<$reference> contains the beggining of file number
720
721
=cut
722
723
sub GetNextIdNotify {
724
    my ($reference) = @_;
725
    my $query = qq|SELECT max(notify_id)
726
         FROM accountlines
727
         WHERE notify_id  like \"$reference%\"
728
         |;
729
730
    # AND borrowernumber=?|;
731
    my $dbh = C4::Context->dbh;
732
    my $sth = $dbh->prepare($query);
733
    $sth->execute();
734
    my $result = $sth->fetchrow;
735
    my $count;
736
    if ( $result eq '' ) {
737
        ( $result = $reference . "01" );
738
    }
739
    else {
740
        $count = substr( $result, 6 ) + 1;
741
742
        if ( $count < 10 ) {
743
            ( $count = "0" . $count );
744
        }
745
        $result = $reference . $count;
746
    }
747
    return $result;
748
}
749
750
=head2 NumberNotifyId
661
=head2 NumberNotifyId
751
662
752
    (@notify) = &NumberNotifyId($borrowernumber);
663
    (@notify) = &NumberNotifyId($borrowernumber);
Lines 799-933 sub AmountNotify{ Link Here
799
    return ($totalnotify);
710
    return ($totalnotify);
800
}
711
}
801
712
802
803
=head2 GetNotifyId
804
805
    ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
806
807
Returns the file number per borrower and itemnumber
808
809
C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
810
from the items tables of the Koha database. Thus,
811
812
C<$itemnumber> contains the borrower categorycode
813
814
C<$notify_id> contains the file number for the borrower number nad item number
815
816
=cut
817
818
sub GetNotifyId {
819
    my ( $borrowernumber, $itemnumber ) = @_;
820
    my $query = qq|SELECT notify_id
821
           FROM accountlines
822
           WHERE borrowernumber=?
823
          AND itemnumber=?
824
           AND (accounttype='FU' or accounttype='O')|;
825
    my $dbh = C4::Context->dbh;
826
    my $sth = $dbh->prepare($query);
827
    $sth->execute( $borrowernumber, $itemnumber );
828
    my ($notify_id) = $sth->fetchrow;
829
    $sth->finish;
830
    return ($notify_id);
831
}
832
833
=head2 CreateItemAccountLine
834
835
    () = &CreateItemAccountLine($borrowernumber, $itemnumber, $date, $amount,
836
                               $description, $accounttype, $amountoutstanding, 
837
                               $timestamp, $notify_id, $level);
838
839
update the account lines with file number or with file level
840
841
C<$items> is a reference-to-hash whose keys are all of the fields
842
from the items tables of the Koha database. Thus,
843
844
C<$itemnumber> contains the item number
845
846
C<$borrowernumber> contains the borrower number
847
848
C<$date> contains the date of the day
849
850
C<$amount> contains item price
851
852
C<$description> contains the descritpion of accounttype 
853
854
C<$accounttype> contains the account type
855
856
C<$amountoutstanding> contains the $amountoutstanding 
857
858
C<$timestamp> contains the timestamp with time and the date of the day
859
860
C<$notify_id> contains the file number
861
862
C<$level> contains the file level
863
864
=cut
865
866
sub CreateItemAccountLine {
867
    my (
868
        $borrowernumber, $itemnumber,  $date,              $amount,
869
        $description,    $accounttype, $amountoutstanding, $timestamp,
870
        $notify_id,      $level
871
    ) = @_;
872
    my $dbh         = C4::Context->dbh;
873
    my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
874
    my $query       = "INSERT into accountlines
875
         (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
876
          VALUES
877
             (?,?,?,?,?,?,?,?,?,?,?)";
878
879
    my $sth = $dbh->prepare($query);
880
    $sth->execute(
881
        $borrowernumber, $nextaccntno,       $itemnumber,
882
        $date,           $amount,            $description,
883
        $accounttype,    $amountoutstanding, $timestamp,
884
        $notify_id,      $level
885
    );
886
}
887
888
=head2 UpdateAccountLines
889
890
    () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
891
892
update the account lines with file number or with file level
893
894
C<$items> is a reference-to-hash whose keys are all of the fields
895
from the items tables of the Koha database. Thus,
896
897
C<$itemnumber> contains the item number
898
899
C<$notify_id> contains the file number
900
901
C<$notify_level> contains the file level
902
903
C<$borrowernumber> contains the borrowernumber
904
905
=cut
906
907
sub UpdateAccountLines {
908
    my ( $notify_id, $notify_level, $borrowernumber, $itemnumber ) = @_;
909
    my $query;
910
    if ( $notify_id eq '' ) {
911
        $query = qq|UPDATE accountlines
912
    SET  notify_level=?
913
    WHERE borrowernumber=? AND itemnumber=?
914
    AND (accounttype='FU' or accounttype='O')|;
915
    } else {
916
        $query = qq|UPDATE accountlines
917
     SET notify_id=?, notify_level=?
918
   WHERE borrowernumber=?
919
    AND itemnumber=?
920
    AND (accounttype='FU' or accounttype='O')|;
921
    }
922
923
    my $sth = C4::Context->dbh->prepare($query);
924
    if ( $notify_id eq '' ) {
925
        $sth->execute( $notify_level, $borrowernumber, $itemnumber );
926
    } else {
927
        $sth->execute( $notify_id, $notify_level, $borrowernumber, $itemnumber );
928
    }
929
}
930
931
=head2 GetItems
713
=head2 GetItems
932
714
933
    ($items) = &GetItems($itemnumber);
715
    ($items) = &GetItems($itemnumber);
Lines 957-985 sub GetItems { Link Here
957
    return ($items);
739
    return ($items);
958
}
740
}
959
741
960
=head2 GetOverdueDelays
961
962
    (@delays) = &GetOverdueDelays($categorycode);
963
964
Returns the list of all delays from overduerules.
965
966
C<@delays> it's an array contains the three delays from overduerules table
967
968
C<$categorycode> contains the borrower categorycode
969
970
=cut
971
972
sub GetOverdueDelays {
973
    my ($category) = @_;
974
    my $query      = qq|SELECT delay1,delay2,delay3
975
                FROM overduerules
976
                WHERE categorycode=?|;
977
    my $sth = C4::Context->dbh->prepare($query);
978
    $sth->execute($category);
979
    my (@delays) = $sth->fetchrow_array;
980
    return (@delays);
981
}
982
983
=head2 GetBranchcodesWithOverdueRules
742
=head2 GetBranchcodesWithOverdueRules
984
743
985
    my @branchcodes = C4::Overdues::GetBranchcodesWithOverdueRules()
744
    my @branchcodes = C4::Overdues::GetBranchcodesWithOverdueRules()
Lines 1000-1066 sub GetBranchcodesWithOverdueRules { Link Here
1000
    return @branches;
759
    return @branches;
1001
}
760
}
1002
761
1003
=head2 CheckAccountLineLevelInfo
1004
1005
    ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
1006
1007
Check and Returns the list of all overdue books.
1008
1009
C<$exist> contains number of line in accounlines
1010
with the same .biblionumber,itemnumber,accounttype,and notify_level
1011
1012
C<$borrowernumber> contains the borrower number
1013
1014
C<$itemnumber> contains item number
1015
1016
C<$accounttype> contains account type
1017
1018
C<$notify_level> contains the accountline level 
1019
1020
1021
=cut
1022
1023
sub CheckAccountLineLevelInfo {
1024
    my ( $borrowernumber, $itemnumber, $level ) = @_;
1025
    my $dbh   = C4::Context->dbh;
1026
    my $query = qq|SELECT count(*)
1027
            FROM accountlines
1028
            WHERE borrowernumber =?
1029
            AND itemnumber = ?
1030
            AND notify_level=?|;
1031
    my $sth = $dbh->prepare($query);
1032
    $sth->execute( $borrowernumber, $itemnumber, $level );
1033
    my ($exist) = $sth->fetchrow;
1034
    return ($exist);
1035
}
1036
1037
=head2 GetOverduerules
1038
1039
    ($overduerules) = &GetOverduerules($categorycode);
1040
1041
Returns the value of borrowers (debarred or not) with notify level
1042
1043
C<$overduerules> return value of debbraed field in overduerules table
1044
1045
C<$category> contains the borrower categorycode
1046
1047
C<$notify_level> contains the notify level
1048
1049
=cut
1050
1051
sub GetOverduerules {
1052
    my ( $category, $notify_level ) = @_;
1053
    my $dbh   = C4::Context->dbh;
1054
    my $query = qq|SELECT debarred$notify_level
1055
                     FROM overduerules
1056
                    WHERE categorycode=?|;
1057
    my $sth = $dbh->prepare($query);
1058
    $sth->execute($category);
1059
    my ($overduerules) = $sth->fetchrow;
1060
    return ($overduerules);
1061
}
1062
1063
1064
=head2 CheckBorrowerDebarred
762
=head2 CheckBorrowerDebarred
1065
763
1066
    ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
764
    ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
Lines 1090-1155 sub CheckBorrowerDebarred { Link Here
1090
}
788
}
1091
789
1092
790
1093
=head2 CheckExistantNotifyid
1094
1095
    ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1096
1097
Check and Returns the notify id if exist else return 0.
1098
1099
C<$exist> contains a notify_id 
1100
1101
C<$borrowernumber> contains the borrower number
1102
1103
C<$date_due> contains the date of item return 
1104
1105
1106
=cut
1107
1108
sub CheckExistantNotifyid {
1109
    my ( $borrowernumber, $date_due ) = @_;
1110
    my $dbh   = C4::Context->dbh;
1111
    my $query = qq|SELECT notify_id FROM accountlines
1112
             LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1113
             WHERE accountlines.borrowernumber =?
1114
              AND date_due = ?|;
1115
    my $sth = $dbh->prepare($query);
1116
    $sth->execute( $borrowernumber, $date_due );
1117
    return $sth->fetchrow || 0;
1118
}
1119
1120
=head2 CheckAccountLineItemInfo
1121
1122
    ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1123
1124
Check and Returns the list of all overdue items from the same file number(notify_id).
1125
1126
C<$exist> contains number of line in accounlines
1127
with the same .biblionumber,itemnumber,accounttype,notify_id
1128
1129
C<$borrowernumber> contains the borrower number
1130
1131
C<$itemnumber> contains item number
1132
1133
C<$accounttype> contains account type
1134
1135
C<$notify_id> contains the file number 
1136
1137
=cut
1138
1139
sub CheckAccountLineItemInfo {
1140
    my ( $borrowernumber, $itemnumber, $accounttype, $notify_id ) = @_;
1141
    my $dbh   = C4::Context->dbh;
1142
    my $query = qq|SELECT count(*) FROM accountlines
1143
             WHERE borrowernumber =?
1144
             AND itemnumber = ?
1145
              AND accounttype= ?
1146
            AND notify_id = ?|;
1147
    my $sth = $dbh->prepare($query);
1148
    $sth->execute( $borrowernumber, $itemnumber, $accounttype, $notify_id );
1149
    my ($exist) = $sth->fetchrow;
1150
    return ($exist);
1151
}
1152
1153
=head2 CheckItemNotify
791
=head2 CheckItemNotify
1154
792
1155
Sql request to check if the document has alreday been notified
793
Sql request to check if the document has alreday been notified
(-)a/t/db_dependent/lib/KohaTest/Overdues.pm (-10 lines)
Lines 21-40 sub methods : Test( 1 ) { Link Here
21
                       GetIssuesIteminfo
21
                       GetIssuesIteminfo
22
                       UpdateFine 
22
                       UpdateFine 
23
                       BorType 
23
                       BorType 
24
                       ReplacementCost 
25
                       GetFine 
24
                       GetFine 
26
                       ReplacementCost2 
27
                       GetNextIdNotify 
28
                       NumberNotifyId
25
                       NumberNotifyId
29
                       AmountNotify
26
                       AmountNotify
30
                       UpdateAccountLines 
31
                       GetItems 
27
                       GetItems 
32
                       GetOverdueDelays 
33
                       CheckAccountLineLevelInfo 
34
                       GetOverduerules
35
                       CheckBorrowerDebarred
28
                       CheckBorrowerDebarred
36
                       CheckExistantNotifyid 
37
                       CheckAccountLineItemInfo 
38
                       CheckItemNotify 
29
                       CheckItemNotify 
39
                       GetOverduesForBranch 
30
                       GetOverduesForBranch 
40
                       AddNotifyLine 
31
                       AddNotifyLine 
41
- 

Return to bug 10256