| | |
| | | -- AND domain__domain_option.domain_option_id = domain_option.domain_option_id |
| | | -- AND domain_option.domain_option_name = 'php' ; |
| | | |
| | | --- Updates related to HSBilling |
| | | -- |
| | | -- Name: packet_component_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| | | -- |
| | | |
| | | UPDATE bank_account SET autodebit_ar = false WHERE autodebit_ar IS NULL; |
| | | ALTER TABLE bank_account ALTER COLUMN autodebit_ar SET NOT NULL; |
| | | CREATE SEQUENCE packet_component_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | |
| | | UPDATE bank_account SET autodebit_ga = false WHERE autodebit_ga IS NULL; |
| | | ALTER TABLE bank_account ALTER COLUMN autodebit_ga SET NOT NULL; |
| | | ALTER TABLE packet_component ADD COLUMN packet_component_id integer |
| | | DEFAULT nextval(('"packet_component_id_seq"'::text)::regclass) NOT NULL; |
| | | |
| | | ALTER TABLE ONLY packet_component |
| | | DROP CONSTRAINT pk_packet_component; |
| | | |
| | | ALTER TABLE ONLY packet_component |
| | | ADD CONSTRAINT pk_packet_component PRIMARY KEY (packet_component_id); |
| | | |
| | | UPDATE bank_account SET autodebit_op = false WHERE autodebit_op IS NULL; |
| | | ALTER TABLE bank_account ALTER COLUMN autodebit_op SET NOT NULL; |
| | | ALTER TABLE ONLY packet ADD COLUMN basepacket_id integer; |
| | | |
| | | ALTER TABLE business_partner ADD COLUMN free boolean NOT NULL DEFAULT false; |
| | | ALTER TABLE business_partner ADD COLUMN indicator_vat character varying(20) NOT NULL DEFAULT 'GROSS'; |
| | | ALTER TABLE business_partner ADD COLUMN exempt_vat boolean NOT NULL DEFAULT false; |
| | | ALTER TABLE business_partner ADD UNIQUE(bp_id); |
| | | |
| | | DROP TABLE billdata CASCADE; |
| | | |
| | | ALTER TABLE basepacket ADD COLUMN article_number integer NOT NULL DEFAULT 0; |
| | | ALTER TABLE basepacket ALTER COLUMN article_number DROP DEFAULT; |
| | | |
| | | ALTER TABLE component ADD COLUMN article_number integer NOT NULL DEFAULT 0; |
| | | ALTER TABLE component ALTER COLUMN article_number DROP DEFAULT; |
| | | ALTER TABLE component DROP CONSTRAINT ckt_component; |
| | | ALTER TABLE component |
| | | ADD CONSTRAINT ckt_component CHECK ( |
| | | (0 <= min_quantity) AND |
| | | (min_quantity <= default_quantity) AND |
| | | (default_quantity <= max_quantity) AND |
| | | (include_quantity <= default_quantity) AND |
| | | (0 <= include_quantity) AND |
| | | (mod(min_quantity, increment_quantity) = 0) AND |
| | | (mod(max_quantity, increment_quantity) = 0) AND |
| | | (mod(default_quantity, increment_quantity) = 0) AND |
| | | (mod(include_quantity, increment_quantity) = 0) |
| | | ); |
| | | |
| | | ALTER TABLE packet DROP COLUMN order_number; |
| | | ALTER TABLE packet DROP COLUMN webserver_group; |
| | | ALTER TABLE packet ADD COLUMN free boolean NOT NULL DEFAULT false; |
| | | |
| | | ALTER TABLE domain DROP COLUMN domain_status; |
| | | ALTER TABLE domain DROP COLUMN domain_status_changed; |
| | | ALTER TABLE domain DROP COLUMN domain_filed; |
| | | ALTER TABLE domain DROP COLUMN domain_until; |
| | | ALTER TABLE domain DROP COLUMN domain_reminder; |
| | | ALTER TABLE domain DROP COLUMN domain_free; |
| | | ALTER TABLE domain DROP COLUMN domain_template; |
| | | |
| | | CREATE TABLE price_list ( |
| | | id serial PRIMARY KEY, |
| | | name varchar(20) |
| | | ); |
| | | INSERT INTO price_list (name) VALUES ('Default Price List'); |
| | | |
| | | CREATE TABLE customer_price_list_mapping ( |
| | | customer integer REFERENCES business_partner(bp_id), |
| | | price_list integer REFERENCES price_list(id), |
| | | PRIMARY KEY (customer, price_list) |
| | | ); |
| | | INSERT INTO customer_price_list_mapping |
| | | (SELECT bp_id, (SELECT id FROM price_list WHERE name LIKE 'Default%') FROM business_partner); |
| | | |
| | | CREATE TABLE price ( |
| | | id serial PRIMARY KEY, |
| | | article_number integer NOT NULL, |
| | | price decimal(10, 2) NOT NULL, |
| | | vat decimal(4,2) NOT NULL, |
| | | price_list integer REFERENCES price_list(id) |
| | | ); |
| | | |
| | | update basecomponent set description='Benutzer, Datenbanken, E-Mail' where basecomponent_id=1; |
| | | update basecomponent set description='%s GB Datentransfervolumen' where basecomponent_id=10; |
| | | update basecomponent set description='%s MB Speicherplatz' where basecomponent_id=11; |
| | | update basecomponent set description='CPU-Thread' where basecomponent_id=19; |
| | | update basecomponent set description='%s MB Arbeitsspeicher' where basecomponent_id=20; |
| | | update basecomponent set description='%s GB Datentransfervolumen' where basecomponent_id=10; |
| | | |
| | | update basepacket set basepacket_code='PAC/SW', description='Statik Web Paket', article_number=1100 where basepacket_id=19; |
| | | update basepacket set basepacket_code='PAC/DW', description='Dynamik Web Paket', article_number=1200 where basepacket_id=20; |
| | | update basepacket set basepacket_code='PAC/WEB', description='Web Paket', article_number=1000 where basepacket_id=21; |
| | | update basepacket set basepacket_code='SRV/MGD', description='Managed Server', article_number=2000 where basepacket_id=22; |
| | | |
| | | insert into price (article_number, price, vat, price_list) values (1, 0, 0, 0, (select id from price_list where name like 'Default%')); |
| | | |
| | | insert into price (article_number, price, vat, price_list) values (100, 8.00, 0.00, (select id from price_list where name like 'Default%')); |
| | | |
| | | insert into price (article_number, price, vat, price_list) values (1000, 2.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1011, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1012, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1013, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1014, 2.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1021, 10.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | |
| | | insert into price (article_number, price, vat, price_list) values (1100, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1111, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1112, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1113, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1114, 2.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1121, 8.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | |
| | | insert into price (article_number, price, vat, price_list) values (1200, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1211, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1212, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1213, 1.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1214, 2.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | insert into price (article_number, price, vat, price_list) values (1221, 8.00, 19.00, (select id from price_list where name like 'Default%')); |
| | | |
| | | update component set article_number=1111 where basepacket_id=19 and basecomponent_id=1; |
| | | update component set article_number=1112 where basepacket_id=19 and basecomponent_id=11; |
| | | update component set article_number=1113 where basepacket_id=19 and basecomponent_id=10; |
| | | update component set article_number=1121 where basepacket_id=19 and basecomponent_id=21; |
| | | |
| | | update component set article_number=1211 where basepacket_id=20 and basecomponent_id=1; |
| | | update component set article_number=1212 where basepacket_id=20 and basecomponent_id=11; |
| | | update component set article_number=1213 where basepacket_id=20 and basecomponent_id=10; |
| | | update component set article_number=1221 where basepacket_id=20 and basecomponent_id=21; |
| | | |
| | | update component set article_number=1011 where basepacket_id=21 and basecomponent_id=1; |
| | | update component set article_number=1012 where basepacket_id=21 and basecomponent_id=11; |
| | | update component set article_number=1013 where basepacket_id=21 and basecomponent_id=10; |
| | | update component set article_number=1021 where basepacket_id=21 and basecomponent_id=21; |
| | | UPDATE packet SET basepacket_id = ( SELECT basepacket_id FROM packet_component |
| | | WHERE packet_component.packet_id = packet.packet_id LIMIT 1 ); |
| | | |
| | | ALTER TABLE ONLY packet |
| | | ADD CONSTRAINT base_packet_ref FOREIGN KEY (basepacket_id) REFERENCES basepacket(basepacket_id); |
| | | |