GAbF99g2

1drop PROCEDURE if exists CorrectAccount;
2drop TABLE  if EXISTS account_2;
3CREATE TABLE `account_2` (
4  `id` bigint(20) NOT NULL AUTO_INCREMENT,
5 `balance` decimal(21,2) DEFAULT '0.00',
6`balance_old` decimal(21,2) DEFAULT '0.00',
7  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2001943 DEFAULT CHARSET=utf8 ;
8
9
10DELIMITER $$
11
12CREATE PROCEDURE CorrectAccount()
13BEGIN
14  DECLARE bDone INT;
15
16  DECLARE cur_account_id bigint(20); 
17  DECLARE cur_account_record_id bigint(20);
18  DECLARE initial_balance decimal(21,2);
19  DECLARE dif_amount decimal(21,2);
20  DECLARE cur_amount decimal(21,2);
21
22  DECLARE curs CURSOR FOR  
23  select a.id, a.balance * (-1), a.balance
24  from account_1 a join (
25	select account_id, sum(amount_rest) as amount_rest 
26	from account_record_1 
27	where oper_type in(0,4,5)
28    group by account_id) as f 
29  on (a.id=f.account_id) 
30  where a.balance+a.balance_inactive  < f.amount_rest and a.balance<0;
31
32  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
33
34  OPEN curs;
35
36  SET bDone = 0;
37  REPEAT
38
39	FETCH curs INTO cur_account_id, dif_amount, initial_balance;
40
41	# REPEAT
42/*
43		select min(id) from account_record_1 where account_id=cur_account_id and amount_rest>0 into cur_account_record_id;
44		select amount_rest from account_record_1 where id=cur_account_record_id into cur_amount;
45
46		if cur_amount >=dif_amount THEN 
47			update account_record_1 set amount_rest=cur_amount-dif_amount where id=cur_account_record_id;
48			update account_1 set balance=balance+dif_amount where id=cur_account_id;
49
50			set dif_amount = 0;
51		ELSE
52			update account_record_1 set amount_rest=0 where id=cur_account_record_id;
53			update account_1 set balance=balance+cur_amount where id=cur_account_id;
54
55			set dif_amount = dif_amount - cur_amount;
56		end if;
57*/
58	insert into account_2(id, balance, balance_old) values(cur_account_id, dif_amount, initial_balance);
59		
60	#UNTIL dif_amount>0 END REPEAT;
61
62  UNTIL bDone END REPEAT;
63
64  CLOSE curs;
65
66END$$
67DELIMITER ;