Re: [1'b68'1 astrad v8] potential list of customers with 'no incoming calls' problems.

[Oussama Hammami <oussama.hammami@switzernet.com>]

La procédure suivante cherche la liste des clients qui changent le port sans que ip change:

delimiter |
DROP PROCEDURE IF EXISTS `Active_Notify`|
CREATE PROCEDURE Active_Notify( IN ST datetime, IN SP datetime)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE USR VARCHAR(11);
  DECLARE cur CURSOR FOR select distinct(username) from location2_history where count=1 and start>ST and stop<SP group by username,domain having count(distinct(port))=count(username) and count(username)>1 and count(distinct(ipaddr))=1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;    
  DROP TABLE IF EXISTS tempo;
    CREATE TEMPORARY TABLE tempo (
    `username` VARCHAR(11),
	PRIMARY KEY  (`username`)
  );
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO USR;
	 IF done THEN
      LEAVE read_loop;
    END IF;		
  IF (select username from location2_history where username=USR and start>ST and stop<SP group by username,count having count(distinct(ipaddr))=1 and count(distinct(port))=SUM(count) and count(*)=SUM(count)) THEN
  INSERT INTO tempo values (USR);
  END IF;	
  END LOOP;
  CLOSE cur;
  SELECT * FROM tempo;
  DELETE FROM tempo;
END|
delimiter ;
      

Résultat d’exécution de cette procédure sur DB3

mysql> select MIN(start) from location2_history ;
+---------------------+
| MIN(start)          |
+---------------------+
| 2011-06-16 15:53:02 |
+---------------------+
1 row in set (0.12 sec)

mysql> CALL Active_Notify('2011-06-16 15:53:02','2011-06-19 00:00:00');
+-------------+
| username    |
+-------------+
| 41215500XXX |
|     ...     |
| 41215500XXX |
+-------------+
120 rows in set (1.29 sec)
      

Résultat d’exécution de cette procédure sur DB4

mysql> select MIN(start) from location2_history ;
+---------------------+
| MIN(start)          |
+---------------------+
| 2011-06-15 10:56:30 |
+---------------------+
1 row in set (0.08 sec)

mysql> CALL Active_Notify('2011-06-15 15:53:02','2011-06-18 00:00:00');
+-------------+
| username    |
+-------------+
| 41215500XXX |
|     ...     |
| 41215500XXX |
+-------------+
92 rows in set (0.73 sec)
      

On 2011-06-24 11:24, Task-by Oussama Hammami wrote:

Avant d'activer le forçage d'expire dans les astrad il faut chercher la liste des clients qui ont eu des problèmes avec les appels entrants.