From d7390031e96e14e3a9ac668662113c58d03ddf1e Mon Sep 17 00:00:00 2001
From: Peter Hormanns <peter.hormanns@jalin.de>
Date: Fri, 21 Mar 2014 18:44:19 +0100
Subject: [PATCH] create customer-alias and -email

---
 hsarback/database/data.sql |  137 ++++++++++++++++++++++++++-------------------
 1 files changed, 78 insertions(+), 59 deletions(-)

diff --git a/hsarback/database/data.sql b/hsarback/database/data.sql
index 77a52ea..87008c1 100644
--- a/hsarback/database/data.sql
+++ b/hsarback/database/data.sql
@@ -2,67 +2,83 @@
 -- Table: basepacket
 --
 INSERT INTO basepacket (basepacket_code, description, sorting, valid, article_number) 
-	VALUES ('DW/B', 'Dynamic-Web/Base', 300, true, 100);
+	VALUES ('PAC/DW', 'Dynamic-Web/Base', 300, true, 100);
 INSERT INTO basepacket (basepacket_code, description, sorting, valid, article_number) 
-	VALUES ('SW/B', 'Static-Web/Base', 400, true, 200);
+	VALUES ('PAC/SW', 'Static-Web/Base', 400, true, 200);
 INSERT INTO basepacket (basepacket_code, description, sorting, valid, article_number) 
-	VALUES ('WEB', 'Web/Base', 100, true, 300);
+	VALUES ('PAC/WEB', 'Web/Base', 100, true, 300);
 INSERT INTO basepacket (basepacket_code, description, sorting, valid, article_number) 
-	VALUES ('HIVE', 'Managed Server', 200, true, 400);
+	VALUES ('SRV/MGD', 'Managed Server', 200, true, 400);
 	
 --
 -- Table: basecomponent
 --
 INSERT INTO basecomponent (basecomponent_code, description, sorting, valid)
-	VALUES ('TRAFFIC', 'Monatliches Datenvolumen in GB', 100, true);
+  	VALUES ('MULTI', 'Accounts', 100, true);
 INSERT INTO basecomponent (basecomponent_code, description, sorting, valid)
-	VALUES ('QUOTA', 'Festplattenspeicherplatz in MB', 200, true);
+	VALUES ('TRAFFIC', 'Monatliches Datenvolumen in GB', 200, true);
 INSERT INTO basecomponent (basecomponent_code, description, sorting, valid)
-	VALUES ('DAEMON', 'Nutzung eines eigenen Servers/Daemons', 300, true);
+	VALUES ('QUOTA', 'Festplattenspeicherplatz in MB', 300, true);
 INSERT INTO basecomponent (basecomponent_code, description, sorting, valid)
-	VALUES ('MIRROR', 'Echtzeitspiegelung der Daten', 400, true);
+	VALUES ('DAEMON', 'Nutzung eines eigenen Servers/Daemons', 400, true);
 INSERT INTO basecomponent (basecomponent_code, description, sorting, valid)
-	VALUES ('BACKUP', 'Nächtliches Backup', 500, true);
+	VALUES ('MIRROR', 'Echtzeitspiegelung der Daten', 500, true);
+INSERT INTO basecomponent (basecomponent_code, description, sorting, valid)
+	VALUES ('BACKUP', 'Nächtliches Backup', 600, true);
+INSERT INTO basecomponent (basecomponent_code, description, sorting, valid)
+	VALUES ('DISK', 'Nächtliches Backup', 700, true);
+INSERT INTO basecomponent (basecomponent_code, description, sorting, valid)
+	VALUES ('CPU', 'Nächtliches Backup', 800, true);
 
 --
 -- Table: component
 --
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 128, 40960, 128, 128, 128, false, 101 FROM basepacket, basecomponent WHERE basepacket_code='DW/B' AND  basecomponent_code='QUOTA';
+	SELECT basepacket_id, basecomponent_id, 1, 10, 1, 1, 1, false, 100 FROM basepacket, basecomponent WHERE basepacket_code='PAC/DW' AND  basecomponent_code='MULTI';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 512, 40960, 512, 512, 512, false, 201 FROM basepacket, basecomponent WHERE basepacket_code='SW/B' AND  basecomponent_code='QUOTA';
+	SELECT basepacket_id, basecomponent_id, 1, 10, 1, 1, 1, false, 200 FROM basepacket, basecomponent WHERE basepacket_code='PAC/SW' AND  basecomponent_code='MULTI';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 512, 40960, 512, 512, 0, false, 301 FROM basepacket, basecomponent WHERE basepacket_code='WEB' AND  basecomponent_code='QUOTA';
+	SELECT basepacket_id, basecomponent_id, 1, 10, 1, 1, 1, false, 300 FROM basepacket, basecomponent WHERE basepacket_code='PAC/WEB' AND  basecomponent_code='MULTI';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 2, 40960, 2, 2, 2, false, 102 FROM basepacket, basecomponent WHERE basepacket_code='DW/B' AND  basecomponent_code='TRAFFIC';
+	SELECT basepacket_id, basecomponent_id, 128, 40960, 128, 128, 128, false, 101 FROM basepacket, basecomponent WHERE basepacket_code='PAC/DW' AND  basecomponent_code='QUOTA';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 4, 40960, 4, 4, 4, false, 202 FROM basepacket, basecomponent WHERE basepacket_code='SW/B' AND  basecomponent_code='TRAFFIC';
+	SELECT basepacket_id, basecomponent_id, 512, 40960, 512, 512, 512, false, 201 FROM basepacket, basecomponent WHERE basepacket_code='PAC/SW' AND  basecomponent_code='QUOTA';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 5, 40960, 5, 5, 0, false, 302 FROM basepacket, basecomponent WHERE basepacket_code='WEB' AND  basecomponent_code='TRAFFIC';
+	SELECT basepacket_id, basecomponent_id, 512, 40960, 512, 512, 0, false, 301 FROM basepacket, basecomponent WHERE basepacket_code='PAC/WEB' AND  basecomponent_code='QUOTA';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 0, 4, 0, 1, 0, false, 103 FROM basepacket, basecomponent WHERE basepacket_code='DW/B' AND  basecomponent_code='DAEMON';
+	SELECT basepacket_id, basecomponent_id, 2, 40960, 2, 2, 2, false, 102 FROM basepacket, basecomponent WHERE basepacket_code='PAC/DW' AND  basecomponent_code='TRAFFIC';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 0, 0, 0, 1, 0, false, 203 FROM basepacket, basecomponent WHERE basepacket_code='SW/B' AND  basecomponent_code='DAEMON';
+	SELECT basepacket_id, basecomponent_id, 4, 40960, 4, 4, 4, false, 202 FROM basepacket, basecomponent WHERE basepacket_code='PAC/SW' AND  basecomponent_code='TRAFFIC';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 0, 4, 0, 1, 0, false, 303 FROM basepacket, basecomponent WHERE basepacket_code='WEB' AND  basecomponent_code='DAEMON';
+	SELECT basepacket_id, basecomponent_id, 5, 40960, 5, 5, 0, false, 302 FROM basepacket, basecomponent WHERE basepacket_code='PAC/WEB' AND  basecomponent_code='TRAFFIC';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 104 FROM basepacket, basecomponent WHERE basepacket_code='DW/B' AND  basecomponent_code='MIRROR';
+	SELECT basepacket_id, basecomponent_id, 0, 4, 0, 1, 0, false, 103 FROM basepacket, basecomponent WHERE basepacket_code='PAC/DW' AND  basecomponent_code='DAEMON';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 204 FROM basepacket, basecomponent WHERE basepacket_code='SW/B' AND  basecomponent_code='MIRROR';
+	SELECT basepacket_id, basecomponent_id, 0, 4, 0, 1, 0, false, 303 FROM basepacket, basecomponent WHERE basepacket_code='PAC/WEB' AND  basecomponent_code='DAEMON';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 304 FROM basepacket, basecomponent WHERE basepacket_code='WEB' AND  basecomponent_code='MIRROR';
+	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 104 FROM basepacket, basecomponent WHERE basepacket_code='PAC/DW' AND  basecomponent_code='MIRROR';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 105 FROM basepacket, basecomponent WHERE basepacket_code='DW/B' AND  basecomponent_code='BACKUP';
+	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 204 FROM basepacket, basecomponent WHERE basepacket_code='PAC/SW' AND  basecomponent_code='MIRROR';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 205 FROM basepacket, basecomponent WHERE basepacket_code='SW/B' AND  basecomponent_code='BACKUP';
+	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 304 FROM basepacket, basecomponent WHERE basepacket_code='PAC/WEB' AND  basecomponent_code='MIRROR';
 INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
-	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 305 FROM basepacket, basecomponent WHERE basepacket_code='WEB' AND  basecomponent_code='BACKUP';
-
+	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 105 FROM basepacket, basecomponent WHERE basepacket_code='PAC/DW' AND  basecomponent_code='BACKUP';
+INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
+	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 205 FROM basepacket, basecomponent WHERE basepacket_code='PAC/SW' AND  basecomponent_code='BACKUP';
+INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
+	SELECT basepacket_id, basecomponent_id, 0, 1, 1, 1, 1, false, 305 FROM basepacket, basecomponent WHERE basepacket_code='PAC/WEB' AND  basecomponent_code='BACKUP';
+INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
+	SELECT basepacket_id, basecomponent_id, 25, 100, 25, 25, 0, false, 410 FROM basepacket, basecomponent WHERE basepacket_code='SRV/MGD' AND  basecomponent_code='DISK';
+INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
+	SELECT basepacket_id, basecomponent_id, 1, 16, 1, 1, 0, false, 420 FROM basepacket, basecomponent WHERE basepacket_code='SRV/MGD' AND  basecomponent_code='CPU';
+INSERT INTO component (basepacket_id, basecomponent_id, min_quantity, max_quantity, default_quantity, increment_quantity, include_quantity, admin_only, article_number)
+	SELECT basepacket_id, basecomponent_id, 10, 1000, 10, 10, 0, false, 430 FROM basepacket, basecomponent WHERE basepacket_code='SRV/MGD' AND  basecomponent_code='TRAFFIC';
+	
 --
 -- Table: business_partner 
 --
-INSERT INTO business_partner (member_id, member_code, member_since, shares_signed)
-	VALUES (10000, 'hsh00-hsh', current_date, 0);
+INSERT INTO business_partner (member_id, member_code, member_since, shares_signed, free, indicator_vat, exempt_vat)
+	VALUES (10000, 'hsh00-hsh', current_date, 0, TRUE, 'NET', FALSE);
 
 --
 -- table: contact 
@@ -76,12 +92,6 @@
 INSERT INTO bank_account (bp_id, autodebit_ga, autodebit_ar, autodebit_op, 
 				bank_customer, bank_account, bank_code, bank_name)
 	SELECT bp_id, false, false, false, '', '', '', '' FROM business_partner WHERE member_id=10000;
-
---
--- table: billdata 
---
-INSERT INTO billdata (bp_id)
-	SELECT bp_id FROM business_partner WHERE member_id=10000;
 
 --
 -- Table: inet_addr 
@@ -112,45 +122,47 @@
 --
 -- table: packet 
 --
-INSERT INTO packet (packet_name, bp_id, hive_id, created, cur_inet_addr_id, free)
-	SELECT 'hsh00', business_partner.bp_id, hive.hive_id, current_date, inet_addr.inet_addr_id, true FROM business_partner, hive, inet_addr 
-		WHERE hive_name='h99' AND inet_addr = inet '176.9.242.72' AND member_id = 10000; 
-INSERT INTO packet (packet_name, bp_id, hive_id, created, cur_inet_addr_id, free)
-	SELECT 'hsh01', business_partner.bp_id, hive.hive_id, current_date, inet_addr.inet_addr_id, true FROM business_partner, hive, inet_addr 
-		WHERE hive_name='h99' AND inet_addr = inet '176.9.242.73' AND member_id = 10000;
+INSERT INTO packet (packet_name, bp_id, hive_id, created, cur_inet_addr_id, free, basepacket_id)
+	SELECT 'hsh00', business_partner.bp_id, hive.hive_id, current_date, inet_addr.inet_addr_id, true, basepacket.basepacket_id 
+		FROM business_partner, hive, inet_addr, basepacket 
+		WHERE hive_name='h99' AND inet_addr = inet '176.9.242.72' AND member_id = 10000 AND basepacket.basepacket_code='PAC/DW'; 
+INSERT INTO packet (packet_name, bp_id, hive_id, created, cur_inet_addr_id, free, basepacket_id)
+	SELECT 'hsh01', business_partner.bp_id, hive.hive_id, current_date, inet_addr.inet_addr_id, true, basepacket.basepacket_id 
+		FROM business_partner, hive, inet_addr, basepacket 
+		WHERE hive_name='h99' AND inet_addr = inet '176.9.242.73' AND member_id = 10000 AND basepacket.basepacket_code='PAC/DW';
 		
 --
 -- table: packet_component 
 --
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 1, packet.packet_id, 2, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 1, packet.packet_id, 2, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh00'; 
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 2, packet.packet_id, 128, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 2, packet.packet_id, 128, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh00'; 
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 3, packet.packet_id, 0, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 3, packet.packet_id, 1, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh00'; 
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 4, packet.packet_id, 0, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 4, packet.packet_id, 1, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh00'; 
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 5, packet.packet_id, 0, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 5, packet.packet_id, 1, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh00'; 
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 1, packet.packet_id, 2, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 1, packet.packet_id, 2, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh01'; 
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 2, packet.packet_id, 128, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 2, packet.packet_id, 128, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh01'; 
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 3, packet.packet_id, 0, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 3, packet.packet_id, 1, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh01'; 
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 4, packet.packet_id, 0, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 4, packet.packet_id, 1, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh01'; 
-INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
-	SELECT 1, 5, packet.packet_id, 0, current_date FROM packet 
+INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created)
+	SELECT 5, packet.packet_id, 1, current_date FROM packet 
 		WHERE packet.packet_name = 'hsh01'; 
 
 --
@@ -167,6 +179,13 @@
 		WHERE packet_name='hsh01'; 
 
 --
+-- table: domain 
+--
+INSERT INTO domain (domain_name, domain_since, domain_dns_master, domain_owner)
+	SELECT 'hostsharing.net', current_date, 'dns.hostsharing.net', unixuser_id FROM unixuser
+		WHERE unixuser.name='hsh00';
+		
+		--
 -- table: domain_option
 --
 INSERT INTO domain_option (domain_option_name) 

--
Gitblit v1.9.0-SNAPSHOT