BiTel

ALTER TABLE ipn_contract_status_$mid ADD PRIMARY KEY (`cid`), DROP INDEX `cid`;

drop table bgbilling.max_dates;
drop table bgbilling.problem_cids;
CREATE TABLE bgbilling.max_dates (
  `dt` DATETIME  NOT NULL,
  `cid` INT  NOT NULL,
  PRIMARY KEY (`cid`),
  INDEX `dt_idx`(`dt`)
);

CREATE TABLE bgbilling.problem_cids (
  `cid` INT  NOT NULL,
  PRIMARY KEY (`cid`)
);

INSERT INTO bgbilling.problem_cids
select cid from bgbilling.ipn_contract_status_1 group by cid having count(*) > 1;



INSERT INTO bgbilling.max_dates(dt, cid )
select max(dt), status.cid from  bgbilling.ipn_contract_status_1  as status
left join bgbilling.ipn_contract_status_log_1 as log ON status.cid = log.cid
where status.cid in (select cid from bgbilling.problem_cids)
group by status.cid ;

delete from bgbilling.ipn_contract_status_1
where
cid in
( select problem_cids.cid from bgbilling.problem_cids );

insert into bgbilling.ipn_contract_status_1( cid, status )
select distinct log.cid, log.action from bgbilling.ipn_contract_status_log_1 as log
left join bgbilling.max_dates on max_dates.dt = log.dt
where
log.cid in
( select problem_cids.cid from bgbilling.problem_cids )
and max_dates.dt is not null
order by log.cid ;

drop table bgbilling.max_dates;
drop table bgbilling.problem_cids;

mysql> show create table ipn_contract_status_9;
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                                                                                        |
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ipn_contract_status_9 | CREATE TABLE `ipn_contract_status_9` (
  `cid` int(10) NOT NULL DEFAULT '0',
  `status` tinyint(3) NOT NULL DEFAULT '0',
  KEY `cid` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Found update for ipn build 245 packet ipn_5.0_252.zip updating to build 252
...
Module: ipn already installed.
Data extract finished...
Extract data => OK
Database updated...
Install ticket inserted..
Base update => OK
Reinit module instanses
REINIT module => 9
Unknown table 'max_dates'
Unknown table 'problem_cids'
Duplicate entry '179736' for key 'PRIMARY'
Module Instance init => OK
Executing call AddSchedulerTasks; param: ipn.sc
Scheduled class bitel.billing.server.ipn.LogCalculator already exists!
Scheduled class bitel.billing.server.ipn.IPNTestGates already exists!
Scheduled class bitel.billing.server.ipn.MaxCalculator already exists!
Result => true
Executing call PutFile; param: ipn.xml:actions
Result => true
Execute calls => OK
File's copy finished...
File copy => OK
Module ipn was successfull installed!
Please, restart BGBilling server.

mysql> DROP TABLE max_dates;
ERROR 1051 (42S02): Unknown table 'max_dates'
mysql> DROP TABLE problem_cids;
ERROR 1051 (42S02): Unknown table 'problem_cids'
mysql> CREATE TABLE max_dates (
    ->   `dt` DATETIME  NOT NULL,
    ->   `cid` INT  NOT NULL,
    ->   PRIMARY KEY (`cid`),
    ->   INDEX `dt_idx`(`dt`)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE problem_cids (
    ->   `cid` INT  NOT NULL,
    ->   PRIMARY KEY (`cid`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO problem_cids
    -> SELECT cid FROM ipn_contract_status_9 group by cid having count(*) > 1;  Query OK, 266 rows affected (0.01 sec)
Records: 266  Duplicates: 0  Warnings: 0

mysql> INSERT INTO max_dates(dt, cid )
    -> SELECT max(dt), status.cid FROM  ipn_contract_status_9  as status
    -> LEFT JOIN ipn_contract_status_log_9 as log ON status.cid = log.cid
    -> WHERE status.cid in ( SELECT cid FROM problem_cids )
    -> GROUP BY status.cid ;
Query OK, 266 rows affected, 249 warnings (0.09 sec)
Records: 266  Duplicates: 0  Warnings: 249

mysql> show warnings;
+---------+------+----------------------------+
| Level   | Code | Message                    |
+---------+------+----------------------------+
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
+---------+------+----------------------------+
64 rows in set (0.00 sec)

mysql> select * from max_dates;
+---------------------+--------+
| dt                  | cid    |
+---------------------+--------+
| 0000-00-00 00:00:00 | 116167 |
| 0000-00-00 00:00:00 | 116313 |
| 0000-00-00 00:00:00 | 116408 |
<...>
| 0000-00-00 00:00:00 | 182610 |
| 2009-12-07 11:34:03 | 165432 |
| 2010-02-24 09:24:17 | 179736 |
| 2010-06-03 11:40:07 | 182678 |
| 2010-06-03 11:40:07 | 182680 |
| 2010-06-08 09:00:03 | 176438 |
| 2010-06-08 10:59:17 | 172426 |
| 2010-06-08 10:59:23 | 178368 |
| 2010-06-08 11:28:05 | 181540 |
| 2010-06-08 11:40:05 | 182744 |
| 2010-06-08 11:40:05 | 182746 |
| 2010-06-09 02:28:07 | 181541 |
| 2010-06-09 02:28:08 | 181542 |
| 2010-06-09 02:28:09 | 181546 |
| 2010-06-09 09:10:03 | 151991 |
| 2010-06-09 09:16:54 | 143386 |
| 2010-06-09 09:51:27 | 181407 |
| 2010-06-09 09:59:17 | 181675 |
+---------------------+--------+
266 rows in set (0.00 sec)

mysql> SELECT * FROM  ipn_contract_status_9  as status LEFT JOIN ipn_contract_status_log_9 as log ON status.cid = log.cid WHERE status.cid =116167;
+--------+--------+------+------+--------+------+---------+
| cid    | status | dt   | cid  | action | uid  | comment |
+--------+--------+------+------+--------+------+---------+
| 116167 |      2 | NULL | NULL |   NULL | NULL | NULL    |
| 116167 |      0 | NULL | NULL |   NULL | NULL | NULL    |
+--------+--------+------+------+--------+------+---------+
2 rows in set (0.00 sec)

mysql> SELECT distinct log.cid, log.action FROM ipn_contract_status_log_9 as log
    -> LEFT JOIN bgbilling.max_dates ON max_dates.dt = log.dt
    -> WHERE
    -> log.cid IN
    -> ( SELECT problem_cids.cid FROM problem_cids )
    -> AND max_dates.dt is NOT NULL
    -> ORDER BY log.cid;
+--------+--------+
| cid    | action |
+--------+--------+
| 143386 |      1 |
| 151991 |      1 |
| 165432 |      2 |
| 172426 |      2 |
| 172426 |      0 |
| 176438 |      0 |
| 178368 |      0 |
| 179736 |      0 |
| 181407 |      0 |
| 181540 |      0 |
| 181541 |      0 |
| 181542 |      0 |
| 181546 |      0 |
| 181675 |      0 |
| 182678 |      2 |
| 182680 |      2 |
| 182744 |      2 |
| 182746 |      2 |
+--------+--------+
18 rows in set (0.08 sec)

mysql> DROP TABLE max_dates;
Query OK, 0 rows affected (0.06 sec)

mysql> DROP TABLE problem_cids;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE max_dates (
    ->   `dt` DATETIME  NOT NULL,
    ->   `cid` INT  NOT NULL,
    ->   PRIMARY KEY (`cid`),
    ->   INDEX `dt_idx`(`dt`)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE problem_cids (
    ->   `cid` INT  NOT NULL,
    ->   PRIMARY KEY (`cid`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO problem_cids
    -> SELECT cid FROM ipn_contract_status_9 group by cid having count(*) > 1;
Query OK, 269 rows affected (0.02 sec)
Records: 269  Duplicates: 0  Warnings: 0

mysql> INSERT INTO max_dates(dt, cid )
    -> SELECT max(dt), status.cid FROM  ipn_contract_status_9  as status
    -> LEFT JOIN ipn_contract_status_log_9 as log ON status.cid = log.cid
    -> WHERE status.cid in ( SELECT cid FROM problem_cids )
    -> GROUP BY status.cid ;
Query OK, 269 rows affected, 252 warnings (0.04 sec)
Records: 269  Duplicates: 0  Warnings: 252

mysql> DELETE FROM ipn_contract_status_9
    -> WHERE
    -> cid in
    -> ( SELECT problem_cids.cid FROM problem_cids );
Query OK, 607 rows affected (0.05 sec)

mysql> INSERT INTO ipn_contract_status_9( cid, status )
    -> SELECT distinct log.cid, log.action FROM ipn_contract_status_log_9 as log
    -> LEFT JOIN bgbilling.max_dates ON max_dates.dt = log.dt and max_dates.cid = log.cid
    -> WHERE
    -> log.cid IN
    -> ( SELECT problem_cids.cid FROM problem_cids )
    -> AND max_dates.dt is NOT NULL
    -> ORDER BY log.cid ;
Query OK, 17 rows affected (0.01 sec)
Records: 17  Duplicates: 0  Warnings: 0

mysql> DROP TABLE max_dates;
Query OK, 0 rows affected (0.04 sec)

mysql> DROP TABLE problem_cids;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE ipn_contract_status_9 ADD PRIMARY KEY (`cid`), DROP INDEX `cid`;
Query OK, 0 rows affected (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

INSERT INTO max_dates(dt, cid )
    -> SELECT max(dt), status.cid FROM  ipn_contract_status_9  as status
    -> LEFT JOIN ipn_contract_status_log_9 as log ON status.cid = log.cid
    -> WHERE status.cid in ( SELECT cid FROM problem_cids )  and log.dt is not null
    -> GROUP BY status.cid ;

delete from  ipn_contract_status_$mid
where
cid not in (select cid from contract_module where mid = $mid );

delete from  ipn_contract_status_log_$mid
where
cid not in (select cid from contract_module where mid = $mid );

DROP TABLE max_dates;
DROP TABLE problem_cids;
CREATE TABLE max_dates (
  `dt` DATETIME  NOT NULL,
  `cid` INT  NOT NULL,
  PRIMARY KEY (`cid`),
  INDEX `dt_idx`(`dt`)
);

CREATE TABLE problem_cids (
  `cid` INT  NOT NULL,
  PRIMARY KEY (`cid`)
);

INSERT INTO problem_cids
SELECT cid FROM ipn_contract_status_$mid group by cid having count(*) > 1;

INSERT INTO max_dates(dt, cid )
SELECT max(dt), status.cid FROM  ipn_contract_status_$mid  as status
LEFT JOIN ipn_contract_status_log_$mid as log ON status.cid = log.cid
WHERE status.cid in ( SELECT cid FROM problem_cids ) and log.dt is not null

GROUP BY status.cid ;

DELETE FROM ipn_contract_status_$mid
WHERE
cid in
( SELECT problem_cids.cid FROM problem_cids );

INSERT INTO ipn_contract_status_$mid( cid, status )
SELECT log.cid, log.action FROM ipn_contract_status_log_$mid as log
LEFT JOIN max_dates ON max_dates.dt = log.dt and max_dates.cid=log.cid
WHERE
log.cid IN
( SELECT problem_cids.cid FROM problem_cids )
AND max_dates.dt is NOT NULL
ORDER BY log.cid ;



DROP TABLE max_dates;
DROP TABLE problem_cids;