createall2.txt

commit;
drop database;
commit;
SET NAMES WIN1251;
create database 'C:/db/nanofon.fdb' user 'SYSDBA' password 'masterkey' DEFAULT CHARACTER SET WIN1251;
connect 'C:/db/nanofon.fdb' user 'SYSDBA' password 'masterkey';

create table persons (
			id int primary key,
			passport decimal(10,0) not null unique check( passport >= 1000000000),
			name varchar(20) not null,
			sec_name varchar(20),
			surname varchar(20) not null,
			birth date not null);
create table tariffs (
			id int primary key,
			tariff_name varchar(20) unique);
create table numbers (
			id int primary key,
			number decimal(10,0) not null unique check( number >= 9210000000 and number <= 9219999999 ));
create table subs (
			id int primary key,
			person int references persons(id) not null,
			number int references numbers(id) not null,
			balance decimal(10,2) not null,
			tariff int references tariffs(id) not null);
create table payments (
			id int primary key,
			sub int references subs(id) not null,
			amount decimal(8,2) not null,
			ptime timestamp not null);
create table sms_prices (
			id int primary key,
			price decimal(6,2) not null);
create table call_prices (
			id int primary key,
			price decimal(6,2) not null);
create table actions (
			id int primary key);
create table transactions (
			id int primary key,
			sub int references subs(id) not null,
			action int references actions(id) not null,
			amount int not null,
			actime timestamp not null,
			tariff int references tariffs(id) not null);
create table bonuses (
			id int primary key,
			name varchar (20) not null unique,
			minutes_amount int,
			sms_amount int,
			payments_amount decimal(10,2),
			init_amount_calls int,
			init_amount_sms int,
			days int);
create table actions_bonuses(
			id int primary key,
			action int references actions(id) not null,
			bonus int references bonuses(id) not null);
create table actions_tariffs(
			id int primary key,
			action int references actions(id) not null,
			tariff int references tariffs(id) not null);
create table subs_bonuses(
			id int primary key,
			sub int references subs(id) not null,
			bonus int references bonuses(id) not null,
			amount_left_sms int,
			amount_left_calls int,
			start_date date,
			expire_date date);			
insert into tariffs values
(1,'Ïðîñòåöêèé');
insert into tariffs values
(2,'SMS-çàâèñèìûé');
insert into tariffs values
(3,'Çâîíèòü ìàìå');
insert into tariffs values
(4,'Âñå ïî 3');
insert into tariffs values
(5,'Ìîäåì');
insert into tariffs values
(6,'Ñòàáèëüíûé');

insert into bonuses values
(1,'Áîëòóí',100,NULL,NULL,NULL,NULL,30);
insert into bonuses values
(2,'Òåëåãðàôèñò',NULL,300,NULL,NULL,10,30);
insert into bonuses values
(3,'Ìåöåíàò',NULL,NULL,400,100,100,NULL);

insert into sms_prices values (1,0.0);
insert into sms_prices values (2,2.0);
insert into sms_prices values (3,5.0);
insert into sms_prices values (10,3.0);

insert into call_prices values (4,0.0);
insert into call_prices values (5,1.0);
insert into call_prices values (6,2.0);
insert into call_prices values (7,3.0);

create table inet_prices (
		id int primary key,
		price decimal(6,2) not null);
		
insert into inet_prices values (8,9.0);
insert into inet_prices values (9,15.0);
insert into inet_prices values (11,3.0);

insert into actions values (1);
insert into actions values (2);
insert into actions values (3);
insert into actions values (4);
insert into actions values (5);
insert into actions values (6);
insert into actions values (7);
insert into actions values (8);
insert into actions values (9);
insert into actions values (10);
insert into actions values (11);

insert into actions_tariffs values (1,3,1);
insert into actions_tariffs values (2,6,1);
insert into actions_tariffs values (3,2,2);
insert into actions_tariffs values (4,7,2);
insert into actions_tariffs values (5,3,3);
insert into actions_tariffs values (6,5,3);
insert into actions_tariffs values (7,8,1);
insert into actions_tariffs values (8,8,2);
insert into actions_tariffs values (9,9,3);

insert into actions_tariffs values (10,10,4);
insert into actions_tariffs values (11,7,4);
insert into actions_tariffs values (12,11,4);
insert into actions_tariffs values (13,3,5);
insert into actions_tariffs values (14,7,5);
insert into actions_tariffs values (15,11,5);
insert into actions_tariffs values (16,3,6);
insert into actions_tariffs values (17,7,6);
insert into actions_tariffs values (18,9,6);


insert into actions_bonuses values (1,5,1);
insert into actions_bonuses values (2,1,2);
insert into actions_bonuses values (3,1,3);
insert into actions_bonuses values (4,4,3);




alter table tariffs add 
			min_balance decimal(10,2) default 0.0 not null;
update tariffs set min_balance = -10.0 where id = 3;
commit;



create sequence gen_payid;
create sequence gen_transid;
	

set term ^ ;
create procedure do_payment (num_id int,amount decimal(8,2),t timestamp ) as
	declare variable sub_id int;
	begin
	sub_id = (select id from subs where number = :num_id);
	insert into payments values (gen_id(gen_payid,1),:sub_id,:amount,:t);
	update subs set balance = balance+:amount where subs.id = :sub_id;
	end ^

create procedure sms (num_id int,amount int) as
	declare variable sub_id int;
	declare variable cost decimal(6,2);
	declare variable act int;
	declare variable tariff int;
	begin
	sub_id = (select id from subs where number = :num_id);
	tariff = (select tariff from subs where id=:sub_id);
	
	cost = (select price from sms_prices,actions_tariffs where sms_prices.id = actions_tariffs.action and actions_tariffs.tariff=:tariff);
	act = (select sms_prices.id from sms_prices,actions_tariffs where sms_prices.id = actions_tariffs.action and actions_tariffs.tariff=:tariff);
	insert into transactions (id,sub,action,amount,actime,tariff) values 
		(gen_id(gen_transid,1),:sub_id,:act,:amount,dateadd(-rand()*31 day to current_timestamp),:tariff);
	update subs set balance = balance - :cost*:amount where subs.id = :sub_id;
	end ^
	
create procedure call (num_id int,amount int) as
	declare variable sub_id int;
	declare variable cost decimal(6,2);
	declare variable act int;
	declare variable tariff int;
	begin
	sub_id = (select id from subs where number = :num_id);
	tariff = (select tariff from subs where id=:sub_id);
	
	cost = (select price from call_prices,actions_tariffs where call_prices.id = actions_tariffs.action and actions_tariffs.tariff=:tariff);
	act = (select call_prices.id from call_prices,actions_tariffs where call_prices.id = actions_tariffs.action and actions_tariffs.tariff=:tariff);
	insert into transactions (id,sub,action,amount,actime,tariff) values 
		(gen_id(gen_transid,1),:sub_id,:act,:amount,dateadd(-rand()*31 day to current_timestamp),:tariff);
	update subs set balance = balance - :cost*:amount where subs.id = :sub_id;
	end ^
	
set term ; ^



commit;

set term ^ ;
create procedure delete_older (d date) as
	begin
	 delete from transactions where actime <=:d;
	end ^
set term ; ^
commit;

create table t_names (id int primary key,name varchar(20));
insert into t_names values (1,'Èâàí');
insert into t_names values (2,'Âëàäèìèð');
insert into t_names values (3,'Ïåòð');
insert into t_names values (4,'Àëåêñàíäð');
insert into t_names values (5,'Ãåííàäèé');
insert into t_names values (6,'Ñåìåí');
insert into t_names values (7,'Ìèõàèë');
insert into t_names values (8,'Àëåêñåé');
insert into t_names values (9,'Àíäðåé');
insert into t_names values (10,'Òèìîôåé');
insert into t_names values (11,'Àôàíàñèé');
insert into t_names values (12,'Ðóñëàí');
insert into t_names values (13,'Èëüÿ');
insert into t_names values (14,'Þðèé');

create table t_sec_names (id int primary key,sec_name varchar(20));
insert into t_sec_names values (1,'Èâàíîâè÷');
insert into t_sec_names values (2,'Âëàäèìèðîâè÷');
insert into t_sec_names values (3,'Ïåòðîâè÷');
insert into t_sec_names values (4,'Àëåêñàíäðîâè÷');
insert into t_sec_names values (5,'Ãåííàäèåâè÷');
insert into t_sec_names values (6,'Ñåìåíîâè÷');
insert into t_sec_names values (7,'Ìèõàéëîâè÷');
insert into t_sec_names values (8,'Àëåêñååâè÷');
insert into t_sec_names values (9,'Àíäðååâè÷');
insert into t_sec_names values (10,'Òèìîôååâè÷');
insert into t_sec_names values (11,'Àôàíàñèåâè÷');
insert into t_sec_names values (12,'Ðóñëàíîâè÷');
insert into t_sec_names values (13,'Èëüè÷');
insert into t_sec_names values (14,'Þðüåâè÷');

create table t_surnames (id int primary key,surname varchar(20));
insert into t_surnames values (1,'Èâàíîâ');
insert into t_surnames values (2,'Ïåòðîâ');
insert into t_surnames values (3,'Ñèäîðîâ');
insert into t_surnames values (4,'Áûêîâ');
insert into t_surnames values (5,'Äóðîâ');
insert into t_surnames values (6,'Ñå÷èí');
insert into t_surnames values (7,'Ëèâøèö');
insert into t_surnames values (8,'Ëàíäàó');

create sequence gen_personid;

set term ^ ;
create procedure fill_persons as
	declare variable i int;
	declare variable pass decimal(10);
	declare variable name varchar(20);
	declare variable sec_name varchar(20);
	declare variable surname varchar(20);
	declare variable days int;
	declare variable random int;
	begin
		i=0;
		while (i < 100000) do
			begin
			pass = 4010000000+rand()*999999;
			if (not exists(select * from persons where passport=:pass)) then
			begin
				random = trunc(rand()*14)+1;
				name = (select name from t_names where id = :random);
				random = trunc(rand()*14)+1;
				sec_name = (select sec_name from t_sec_names where id = :random);
				random = trunc(rand()*8)+1;
				surname = (select surname from t_surnames where id = :random);
				days = -rand()*30*12*50;
				insert into persons values (gen_id(gen_personid,1),:pass,:name,:sec_name,:surname,
				dateadd(:days day to date '1995-01-01'));
				i=i+1;
			end
			end
	end ^
set term ; ^
commit;

execute procedure fill_persons;
select  count(*) as persons from persons;

create sequence gen_numid;

set term ^ ;
create procedure fill_numbers  as
	declare variable i int;
	declare variable num decimal(10);
	begin
		i=0;
		while (i < 110000) do
			begin
			num = 9210000000+rand()*9999999;
			if (not exists(select * from numbers where number=:num)) then
			begin
				insert into numbers values (gen_id(gen_numid,1),:num);
				i=i+1;
			end
			end
	end ^
set term ; ^
commit;

execute procedure fill_numbers;
select  count(*) as numbers from numbers;


set term ^ ;
create procedure fill_subs  as
	declare variable i int;
	begin
		i=1;
		while (i <= 100000) do
			begin
			insert into subs values (:i,:i,:i,rand()*20,rand()*5+1);
			i=i+1;
			end
	end ^
set term ; ^
commit;

execute procedure fill_subs;
select  count(*) as subs from subs;

set term ^ ;
create procedure fill_payments  as
	declare variable i int;
	begin
		i=1;
		while (i <= 100000) do
			begin
			execute procedure do_payment (rand()*99999+1,rand()*90+10,dateadd(-rand()*31 day to current_timestamp));
			i=i+1;
			end
	end ^
set term ; ^
commit;

execute procedure fill_payments;
select  count(*) as payments from payments;

set term ^ ;
create procedure fill_actions  as
	declare variable i int;
	begin
		i=1;
		while (i <= 50000) do
			begin
			execute procedure call(rand()*99999+1,rand()*29+1);
			execute procedure sms(rand()*99999+1,1);
			i=i+1;
			end
	end ^
set term ; ^
commit;

execute procedure fill_actions;
select  count(*) as transactions from transactions;



commit;