mirror of https://github.com/sipwise/db-schema.git
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
72 lines
2.4 KiB
72 lines
2.4 KiB
USE billing;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
BEGIN WORK;
|
|
|
|
ALTER TABLE contracts
|
|
ADD COLUMN vat_rate TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
|
|
ADD COLUMN add_vat TINYINT(1) UNSIGNED NOT NULL DEFAULT 0;
|
|
|
|
-- preserve the vat information by copying it from billing_profiles to contracts
|
|
UPDATE contracts c
|
|
JOIN billing_mappings m ON c.id = m.contract_id AND
|
|
m.id = (
|
|
SELECT mi.id FROM billing_mappings mi WHERE mi.contract_id = c.id
|
|
ORDER BY start_date DESC LIMIT 1)
|
|
JOIN billing_profiles p ON m.billing_profile_id = p.id
|
|
SET
|
|
c.vat_rate = p.vat_rate,
|
|
c.add_vat = p.vat_included;
|
|
|
|
ALTER TABLE billing_profiles
|
|
DROP COLUMN vat_rate,
|
|
DROP COLUMN vat_included;
|
|
|
|
DROP TABLE IF EXISTS invoice_templates;
|
|
CREATE TABLE `invoice_templates` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`reseller_id` int(11) unsigned NOT NULL,
|
|
`name` varchar(255) NOT NULL DEFAULT '',
|
|
`type` enum('svg','html') NOT NULL DEFAULT 'svg',
|
|
`is_active` tinyint(1) NOT NULL DEFAULT '0',
|
|
`data` mediumblob,
|
|
PRIMARY KEY (`id`),
|
|
CONSTRAINT `invoice_templates_ibfk_1` FOREIGN KEY (`reseller_id`) REFERENCES `resellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
DROP TABLE IF EXISTS invoices;
|
|
CREATE TABLE `invoices` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`contract_id` int(11) unsigned NOT NULL,
|
|
`serial` varchar(32) NOT NULL,
|
|
`period_start` datetime NOT NULL,
|
|
`period_end` datetime NOT NULL,
|
|
`amount_net` double NOT NULL DEFAULT '0',
|
|
`amount_vat` double NOT NULL DEFAULT '0',
|
|
`amount_total` double NOT NULL DEFAULT '0',
|
|
`data` blob,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `serial_idx` (`serial`),
|
|
CONSTRAINT `invoice_contract_fk` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
DROP TABLE IF EXISTS invoice_template_resources;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
alter table contacts
|
|
add column bank_name varchar(255) default null,
|
|
add column gpp0 varchar(255) default null,
|
|
add column gpp1 varchar(255) default null,
|
|
add column gpp2 varchar(255) default null,
|
|
add column gpp3 varchar(255) default null,
|
|
add column gpp4 varchar(255) default null,
|
|
add column gpp5 varchar(255) default null,
|
|
add column gpp6 varchar(255) default null,
|
|
add column gpp7 varchar(255) default null,
|
|
add column gpp8 varchar(255) default null,
|
|
add column gpp9 varchar(255) default null;
|
|
|
|
COMMIT;
|