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 2681-2730
CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou
Link Here
|
2681 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
2682 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
2682 |
|
2683 |
|
2683 |
-- |
2684 |
-- |
2684 |
-- Table structure for table `accountlines` |
|
|
2685 |
-- |
2686 |
|
2687 |
DROP TABLE IF EXISTS `accountlines`; |
2688 |
CREATE TABLE `accountlines` ( |
2689 |
`accountlines_id` int(11) NOT NULL AUTO_INCREMENT, |
2690 |
`borrowernumber` int(11) NOT NULL default 0, |
2691 |
`accountno` smallint(6) NOT NULL default 0, |
2692 |
`itemnumber` int(11) default NULL, |
2693 |
`date` date default NULL, |
2694 |
`amount` decimal(28,6) default NULL, |
2695 |
`description` mediumtext, |
2696 |
`dispute` mediumtext, |
2697 |
`accounttype` varchar(5) default NULL, |
2698 |
`amountoutstanding` decimal(28,6) default NULL, |
2699 |
`lastincrement` decimal(28,6) default NULL, |
2700 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
2701 |
`notify_id` int(11) NOT NULL default 0, |
2702 |
`notify_level` int(2) NOT NULL default 0, |
2703 |
`note` text NULL default NULL, |
2704 |
`manager_id` int(11) NULL, |
2705 |
PRIMARY KEY (`accountlines_id`), |
2706 |
KEY `acctsborridx` (`borrowernumber`), |
2707 |
KEY `timeidx` (`timestamp`), |
2708 |
KEY `itemnumber` (`itemnumber`), |
2709 |
CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
2710 |
CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL |
2711 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
2712 |
|
2713 |
-- |
2714 |
-- Table structure for table `accountoffsets` |
2715 |
-- |
2716 |
|
2717 |
DROP TABLE IF EXISTS `accountoffsets`; |
2718 |
CREATE TABLE `accountoffsets` ( |
2719 |
`borrowernumber` int(11) NOT NULL default 0, |
2720 |
`accountno` smallint(6) NOT NULL default 0, |
2721 |
`offsetaccount` smallint(6) NOT NULL default 0, |
2722 |
`offsetamount` decimal(28,6) default NULL, |
2723 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
2724 |
CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE |
2725 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
2726 |
|
2727 |
-- |
2728 |
-- Table structure for table `action_logs` |
2685 |
-- Table structure for table `action_logs` |
2729 |
-- |
2686 |
-- |
2730 |
|
2687 |
|
Lines 3390-3395
CREATE TABLE IF NOT EXISTS marc_modification_template_actions (
Link Here
|
3390 |
CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE |
3347 |
CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE |
3391 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
3348 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
3392 |
|
3349 |
|
|
|
3350 |
-- |
3351 |
-- Table structure for table 'account_credits' |
3352 |
-- |
3353 |
DROP TABLE IF EXISTS account_credits; |
3354 |
CREATE TABLE IF account_credits ( |
3355 |
credit_id int(11) NOT NULL AUTO_INCREMENT, -- The unique id for this credit |
3356 |
borrowernumber int(11) NOT NULL, -- The borrower this credit applies to |
3357 |
`type` varchar(255) NOT NULL, -- The type of credit this is ( defined by Koha::Accounts::CreditTypes ) |
3358 |
amount_received decimal(28,6) DEFAULT NULL, -- If this was a cash payment, the amount of money given |
3359 |
amount_paid decimal(28,6) NOT NULL, -- The actual ammount paid, if less than amount_recieved, change was given back |
3360 |
amount_remaining decimal(28,6) NOT NULL, -- The amount of this credit that has not been applied to outstanding debits |
3361 |
notes text, -- Misc notes for this credit |
3362 |
branchcode VARCHAR( 10 ) NULL DEFAULT NULL, -- Branchcode where the credit was created ( if any ) |
3363 |
manager_id int(11) DEFAULT NULL, -- The borrowernumber of the user who created this credit ( if any ) |
3364 |
created_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was created |
3365 |
updated_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was last modified |
3366 |
PRIMARY KEY (credit_id), |
3367 |
KEY borrowernumber (borrowernumber), |
3368 |
KEY branchcode (branchcode) |
3369 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
3370 |
|
3371 |
-- |
3372 |
-- Constraints for table `account_credits` |
3373 |
-- |
3374 |
ALTER TABLE `account_credits` |
3375 |
ADD CONSTRAINT account_credits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, |
3376 |
ADD CONSTRAINT account_credits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; |
3377 |
|
3378 |
-- |
3379 |
-- Table structure for table 'account_debits' |
3380 |
-- |
3381 |
|
3382 |
DROP TABLE IF EXISTS account_debits; |
3383 |
CREATE TABLE account_debits ( |
3384 |
debit_id int(11) NOT NULL AUTO_INCREMENT, -- The unique id for this debit |
3385 |
borrowernumber int(11) NOT NULL DEFAULT '0', -- The borrower this debit applies to |
3386 |
itemnumber int(11) DEFAULT NULL, -- The item related to this debit ( for fines, lost fees, etc ) |
3387 |
issue_id int(11) DEFAULT NULL, -- The checkout this debit is related to ( again, for fines, lost fees, etc ) |
3388 |
`type` varchar(255) NOT NULL, -- The type of debit this is ( defined by Koha::Accounts::DebitTypes ) |
3389 |
accruing tinyint(1) NOT NULL DEFAULT '0', -- Boolean flag, tells of if this is a fine that is still accruing |
3390 |
amount_original decimal(28,6) DEFAULT NULL, -- The total amount of this debit |
3391 |
amount_outstanding decimal(28,6) DEFAULT NULL, -- The amount still owed on this debit |
3392 |
amount_last_increment decimal(28,6) DEFAULT NULL, -- The amount by which this debit last changed |
3393 |
description mediumtext, -- The description for this debit |
3394 |
notes text, -- Misc notes for this debit |
3395 |
branchcode VARCHAR( 10 ) NULL DEFAULT NULL, -- Branchcode where the debit was created ( if any ) |
3396 |
manager_id int(11) DEFAULT NULL, -- The borrowernumber of the user who created this debit ( if any ) |
3397 |
created_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was created |
3398 |
updated_on timestamp NULL DEFAULT NULL, -- Timestamp for when this credit was last modified |
3399 |
PRIMARY KEY (debit_id), |
3400 |
KEY acctsborridx (borrowernumber), |
3401 |
KEY itemnumber (itemnumber), |
3402 |
KEY borrowernumber (borrowernumber), |
3403 |
KEY issue_id (issue_id), |
3404 |
KEY branchcode (branchcode) |
3405 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
3406 |
|
3407 |
-- |
3408 |
-- Constraints for table `account_debits` |
3409 |
-- |
3410 |
ALTER TABLE `account_debits` |
3411 |
ADD CONSTRAINT account_debits_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, |
3412 |
ADD CONSTRAINT account_debits_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE; |
3413 |
|
3414 |
-- |
3415 |
-- Table structure for table 'account_offsets' |
3416 |
-- |
3417 |
|
3418 |
DROP TABLE IF EXISTS account_offsets; |
3419 |
CREATE TABLE account_offsets ( |
3420 |
offset_id int(11) NOT NULL AUTO_INCREMENT, -- Unique id for this offset |
3421 |
debit_id int(11) DEFAULT NULL, -- Related debit |
3422 |
credit_id int(11) DEFAULT NULL, -- Related credit ( if any ) |
3423 |
`type` varchar(255) DEFAULT NULL, -- The type of this offset ( defined by Koha::Accounts::OffsetTypes ), if any |
3424 |
amount decimal(28,6) NOT NULL, -- The amount of the offset, positive means patron owes more, negative means patron owes less |
3425 |
created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Timestamp for when this offset was created |
3426 |
PRIMARY KEY (offset_id), |
3427 |
KEY fee_id (debit_id), |
3428 |
KEY payment_id (credit_id) |
3429 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
3430 |
|
3431 |
-- |
3432 |
-- Constraints for table `account_offsets` |
3433 |
-- |
3434 |
ALTER TABLE `account_offsets` |
3435 |
ADD CONSTRAINT account_offsets_ibfk_1 FOREIGN KEY (debit_id) REFERENCES account_debits (debit_id) ON DELETE CASCADE ON UPDATE CASCADE, |
3436 |
ADD CONSTRAINT account_offsets_ibfk_2 FOREIGN KEY (credit_id) REFERENCES account_credits (credit_id) ON DELETE CASCADE ON UPDATE CASCADE; |
3437 |
|
3438 |
|
3393 |
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
3439 |
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
3394 |
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
3440 |
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
3395 |
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
3441 |
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |