|
Lines 265-270
CREATE TABLE `borrowers` ( -- this table includes information about your patrons
Link Here
|
| 265 |
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower |
265 |
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower |
| 266 |
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) |
266 |
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) |
| 267 |
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history |
267 |
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history |
|
|
268 |
`account_balance` decimal(28,6) NOT NULL, |
| 268 |
UNIQUE KEY `cardnumber` (`cardnumber`), |
269 |
UNIQUE KEY `cardnumber` (`cardnumber`), |
| 269 |
PRIMARY KEY `borrowernumber` (`borrowernumber`), |
270 |
PRIMARY KEY `borrowernumber` (`borrowernumber`), |
| 270 |
KEY `categorycode` (`categorycode`), |
271 |
KEY `categorycode` (`categorycode`), |
|
Lines 2739-2788
CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou
Link Here
|
| 2739 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
2740 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2740 |
|
2741 |
|
| 2741 |
-- |
2742 |
-- |
| 2742 |
-- Table structure for table `accountlines` |
|
|
| 2743 |
-- |
| 2744 |
|
| 2745 |
DROP TABLE IF EXISTS `accountlines`; |
| 2746 |
CREATE TABLE `accountlines` ( |
| 2747 |
`accountlines_id` int(11) NOT NULL AUTO_INCREMENT, |
| 2748 |
`borrowernumber` int(11) NOT NULL default 0, |
| 2749 |
`accountno` smallint(6) NOT NULL default 0, |
| 2750 |
`itemnumber` int(11) default NULL, |
| 2751 |
`date` date default NULL, |
| 2752 |
`amount` decimal(28,6) default NULL, |
| 2753 |
`description` mediumtext, |
| 2754 |
`dispute` mediumtext, |
| 2755 |
`accounttype` varchar(5) default NULL, |
| 2756 |
`amountoutstanding` decimal(28,6) default NULL, |
| 2757 |
`lastincrement` decimal(28,6) default NULL, |
| 2758 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
| 2759 |
`notify_id` int(11) NOT NULL default 0, |
| 2760 |
`notify_level` int(2) NOT NULL default 0, |
| 2761 |
`note` text NULL default NULL, |
| 2762 |
`manager_id` int(11) NULL, |
| 2763 |
PRIMARY KEY (`accountlines_id`), |
| 2764 |
KEY `acctsborridx` (`borrowernumber`), |
| 2765 |
KEY `timeidx` (`timestamp`), |
| 2766 |
KEY `itemnumber` (`itemnumber`), |
| 2767 |
CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 2768 |
CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL |
| 2769 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2770 |
|
| 2771 |
-- |
| 2772 |
-- Table structure for table `accountoffsets` |
| 2773 |
-- |
| 2774 |
|
| 2775 |
DROP TABLE IF EXISTS `accountoffsets`; |
| 2776 |
CREATE TABLE `accountoffsets` ( |
| 2777 |
`borrowernumber` int(11) NOT NULL default 0, |
| 2778 |
`accountno` smallint(6) NOT NULL default 0, |
| 2779 |
`offsetaccount` smallint(6) NOT NULL default 0, |
| 2780 |
`offsetamount` decimal(28,6) default NULL, |
| 2781 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
| 2782 |
CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE |
| 2783 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2784 |
|
| 2785 |
-- |
| 2786 |
-- Table structure for table `action_logs` |
2743 |
-- Table structure for table `action_logs` |
| 2787 |
-- |
2744 |
-- |
| 2788 |
|
2745 |
|
|
Lines 3520-3525
CREATE TABLE items_search_fields (
Link Here
|
| 3520 |
ON DELETE SET NULL ON UPDATE CASCADE |
3477 |
ON DELETE SET NULL ON UPDATE CASCADE |
| 3521 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
3478 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3522 |
|
3479 |
|
|
|
3480 |
-- |
| 3481 |
-- Table structure for table 'account_credits' |
| 3482 |
-- |
| 3483 |
DROP TABLE IF EXISTS account_credits; |
| 3484 |
CREATE TABLE IF account_credits ( |
| 3485 |
credit_id int(11) NOT NULL AUTO_INCREMENT, -- The unique id for this credit |
| 3486 |
borrowernumber int(11) NOT NULL, -- The borrower this credit applies to |
| 3487 |
`type` varchar(255) NOT NULL, -- The type of credit this is ( defined by Koha::Accounts::CreditTypes ) |
| 3488 |
amount_received decimal(28,6) DEFAULT NULL, -- If this was a cash payment, the amount of money given |
| 3489 |
amount_paid decimal(28,6) NOT NULL, -- The actual ammount paid, if less than amount_recieved, change was given back |
| 3490 |
amount_remaining decimal(28,6) NOT NULL, -- The amount of this credit that has not been applied to outstanding debits |
| 3491 |
amount_voided decimal(28,6) NULL DEFAULT NULL, -- The amount of this credit was for before it was voided |
| 3492 |
notes text, -- Misc notes for this credit |
| 3493 |
branchcode VARCHAR( 10 ) NULL DEFAULT NULL, -- Branchcode where the credit was created ( if any ) |
| 3494 |
manager_id int(11) DEFAULT NULL, -- The borrowernumber of the user who created this credit ( if any ) |
| 3495 |
created_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was created |
| 3496 |
updated_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was last modified |
| 3497 |
PRIMARY KEY (credit_id), |
| 3498 |
KEY borrowernumber (borrowernumber), |
| 3499 |
KEY branchcode (branchcode) |
| 3500 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 3501 |
|
| 3502 |
-- |
| 3503 |
-- Constraints for table `account_credits` |
| 3504 |
-- |
| 3505 |
ALTER TABLE `account_credits` |
| 3506 |
ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3507 |
ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; |
| 3508 |
|
| 3509 |
-- |
| 3510 |
-- Table structure for table 'account_debits' |
| 3511 |
-- |
| 3512 |
|
| 3513 |
DROP TABLE IF EXISTS account_debits; |
| 3514 |
CREATE TABLE account_debits ( |
| 3515 |
debit_id int(11) NOT NULL AUTO_INCREMENT, -- The unique id for this debit |
| 3516 |
borrowernumber int(11) NOT NULL DEFAULT '0', -- The borrower this debit applies to |
| 3517 |
itemnumber int(11) DEFAULT NULL, -- The item related to this debit ( for fines, lost fees, etc ) |
| 3518 |
issue_id int(11) DEFAULT NULL, -- The checkout this debit is related to ( again, for fines, lost fees, etc ) |
| 3519 |
`type` varchar(255) NOT NULL, -- The type of debit this is ( defined by Koha::Accounts::DebitTypes ) |
| 3520 |
accruing tinyint(1) NOT NULL DEFAULT '0', -- Boolean flag, tells of if this is a fine that is still accruing |
| 3521 |
amount_original decimal(28,6) DEFAULT NULL, -- The total amount of this debit |
| 3522 |
amount_outstanding decimal(28,6) DEFAULT NULL, -- The amount still owed on this debit |
| 3523 |
amount_last_increment decimal(28,6) DEFAULT NULL, -- The amount by which this debit last changed |
| 3524 |
description mediumtext, -- The description for this debit |
| 3525 |
notes text, -- Misc notes for this debit |
| 3526 |
branchcode VARCHAR( 10 ) NULL DEFAULT NULL, -- Branchcode where the debit was created ( if any ) |
| 3527 |
manager_id int(11) DEFAULT NULL, -- The borrowernumber of the user who created this debit ( if any ) |
| 3528 |
created_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was created |
| 3529 |
updated_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was last modified |
| 3530 |
PRIMARY KEY (debit_id), |
| 3531 |
KEY acctsborridx (borrowernumber), |
| 3532 |
KEY itemnumber (itemnumber), |
| 3533 |
KEY borrowernumber (borrowernumber), |
| 3534 |
KEY issue_id (issue_id), |
| 3535 |
KEY branchcode (branchcode) |
| 3536 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 3537 |
|
| 3538 |
-- |
| 3539 |
-- Constraints for table `account_debits` |
| 3540 |
-- |
| 3541 |
ALTER TABLE `account_debits` |
| 3542 |
ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3543 |
ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; |
| 3544 |
|
| 3545 |
-- |
| 3546 |
-- Table structure for table 'account_offsets' |
| 3547 |
-- |
| 3548 |
|
| 3549 |
DROP TABLE IF EXISTS account_offsets; |
| 3550 |
CREATE TABLE account_offsets ( |
| 3551 |
offset_id int(11) NOT NULL AUTO_INCREMENT, -- Unique id for this offset |
| 3552 |
debit_id int(11) DEFAULT NULL, -- Related debit |
| 3553 |
credit_id int(11) DEFAULT NULL, -- Related credit ( if any ) |
| 3554 |
`type` varchar(255) DEFAULT NULL, -- The type of this offset ( defined by Koha::Accounts::OffsetTypes ), if any |
| 3555 |
amount decimal(28,6) NOT NULL, -- The amount of the offset, positive means patron owes more, negative means patron owes less |
| 3556 |
created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp for when this offset was created |
| 3557 |
PRIMARY KEY (offset_id), |
| 3558 |
KEY fee_id (debit_id), |
| 3559 |
KEY payment_id (credit_id) |
| 3560 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 3561 |
|
| 3562 |
-- |
| 3563 |
-- Constraints for table `account_offsets` |
| 3564 |
-- |
| 3565 |
ALTER TABLE `account_offsets` |
| 3566 |
ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3567 |
ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 3568 |
|
| 3523 |
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
3569 |
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
| 3524 |
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
3570 |
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
| 3525 |
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
3571 |
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |