- Lo primero que se hace es crear una tabla temporal con la
siguientes campos:
CREATE TEMPORARY TABLE temp(
login VARCHAR(8),
tarifacion VARCHAR(10),
inicio DATETIME,
fin DATETIME,
tarifa FLOAT);
- Se genera la cláusula que se aplicará en la sentencia
para seleccionar las filas implicadas en la facturación.
Hay que tener en cuenta si se ha especificado un login, un
grupo, o una ubicación concreta. En tal caso, hay que
añadir a la cláusula parámetros tales como: ubicacion='UBICACION1'
o login='LOGIN1'.
En nuestro ejemplo se tienen dos cláusulas diferentes: una para el histórico de activación,
y la otra para el histórico de conexiones.
En la cláusula del histórico es suficiente añadir
grupo='GRUPO1'. La cláusula del conexiones es un poco
más complicada, ya que en la tabla hco_conexiones no
está el campo grupo. Lo que se hace es un JOIN entre las tablas
usuarios y hco_conexiones. La cláusula de conexiones
empieza por hco_conexiones.login = usuarios.login
AND usuarios.grupo = 'GRUPO1' .
- Ya tenemos el grupo de usuarios que hay que seleccionar. Vamos a
seguir delimitando la búsqueda, en primer lugar habrá que ver si
estamos facturando o haciendo un informe de consumo. Si estamos
facturando se añade a las cláusulas AND facturado='no'. De
esta manera sólo se tnedrán en cuenta las filas que no estén
facturadas previamente.
La última restricción ha aplicar es la fecha. Se van a tomar
todas las filas en las que se cumpla que: han empezado antes
de la FECHA_FIN, y que han terminado después de FECHA_INICIO o
todavía no han terminado.
- Ya tenemos las dos sentencias que nos van a rellenar
la tabla temp con todas las filas implicadas en la facturación:
INSERT INTO temp
SELECT login,tarifacion,alta,baja,tarifa
FROM hco_activacion
WHERE (grupo = 'GRUPO1' AND
alta < 'FECHA_FIN' AND
(baja > 'FECHA_INICIO' OR baja IS NULL) AND
tarifacion !='pospago');
INSERT INTO temp
SELECT hco_conexiones.login,tarifacion,alta,baja,tarifa
FROM hco_activacion,usuarios
WHERE (hco_conexiones.login = usuarios.login AND
usuarios.grupo = 'GRUPO1' AND
inicio < 'FECHA_FIN') AND
(fin > 'FECHA_INICIO' OR fin IS NULL) AND
tarifacion ='pospago');
Con estas sentencia ya tenemos todas las filas implicadas en una misma
tabla. En la figura 4.1 vemos los tres tipos de
filas que puede contener la tabla según
su fecha de inicio y su fecha de fin:
Figura 4.1:
Tipos de filas dentro de la tabla temp.
|
|
A las filas de tipo uno no hay que hacerles nada. Se facturan tal cual están.
A las filas de tipo dos se actualiza el tiempo de fin con el
instante actual. Esto se está realizando en una tabla temporal,
así que no tendrá repercusión en el histórico.
UPDATE temp SET fin='FECHA_FIN'
WHERE fin > 'FECHA_FIN' OR fin IS NULL;
Las filas del tipo tres sólo van a tener lugar cuando estemos
realizando un informe de consumo, ya que en la facturación
el intervalo de consulta tiene como inicio la fecha 0000-00-00.
En los casos de informe las filas de tipo tres se cambiarán con
la sentencia:
UPDATE temp SET inicio='FECHA_INICIO'
WHERE inicio < 'FECHA_INICIO';
- Después de hacer esto en la tabla temporal temp
están todas las filas que hay que tarificar con la fecha de inicio,
y la fecha de fin adecuadas. Es el momento de crear otra
tabla temporal llamada tabla_consumo con el consumo
de cada login:
CREATE TEMPORARY TABLE tabla_consumo(
login VARCHAR(8),
tarifacion VARCHAR (8),
importe FLOAT);
- Rellenamos en la tabla de consumo la filas de tarifa plana diaria:
INSERT INTO tabla_consumo
SELECT login,tarifacion,
(TO_DAYS(fin)-TO_DAYS(inicio))*tarifa
FROM temp WHERE tarifacion='pdiaria';
- Tarifa Plana Mensual:
INSERT INTO tabla_consumo
SELECT login,tarifacion,
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM fin),
EXTRACT(YEAR_MONTH FROM inicio))*tarifa +
(TO_DAYS(fin)-
TO_DAYS(DATE_ADD(inicio,INTERVAL
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM fin),
EXTRACT(YEAR_MONTH FROM inicio)) MONTH)))* tarifa/30
FROM temp WHERE tarifacion='pmensual';
- Bono.
INSERT INTO tabla_consumo
SELECT login,tarifacion,tarifa FROM temp
WHERE tarifacion='bono';
- Pospago.
INSERT INTO tabla_consumo
SELECT login,tarifacion,
((TO_DAYS(fin)-TO_DAYS(inicio))*86400 +
TIME_TO_SEC(EXTRACT(HOUR_SECOND FROM fin))-
TIME_TO_SEC(EXTRACT(HOUR_SECOND FROM inicio))
)*tarifa/3600
FROM temp WHERE tarifacion='pospago';
- Como resultado de esto nos queda la tabla tabla_consumo
con filas que contienen, el login, el tipo de tarifación y
el importe. Para calcular el total de un usuario sólo
tenemos que usar la sentencia:
SELECT sum(importe) FROM tabla_consumo
WHERE login='LOGIN1';
Para saber el importe de cada tipo se basta con la sentencia:
SELECT sum(importe) FROM tabla_detalle
WHERE login='LOGIN1' AND tarifacion='TARIFA';
![\includegraphics[height=11.4cm,width=9.5cm
]{crono.ps}](img30.png)