Locatech GPS - Panel de control

Usuario seleccionado...

versión: v4.6.0

Cuentas

Dispositivos

Usuarios

Renovaciones

DROP TEMPORARY TABLE IF EXISTS tc_statistics_total;
CREATE TEMPORARY TABLE tc_statistics_total AS
select
  `tc_accounts`.`appname` AS `appname`,
  'Cuentas'  AS `Tipo`,
  left (`tc_accounts`.`created`, 7) AS `Fecha`,
  `CAPITALIZE`(monthname(`tc_accounts`.`created`)) AS `Mes`,
  count(0) AS `Total`
from
  `tc_accounts`
where
  ((`tc_accounts`.`created` < last_day((curdate() + interval 1 day))) and
  (year (`tc_accounts`.`created`) = :varAno) and
  (`tc_accounts`.`test` = 0) and `tc_accounts`.`appname` = :varappname)
group by
  `tc_accounts`.`appname`,
  left (`tc_accounts`.`created`, 7)
union
select
  `tc_devices`.`appname` AS `appname`,
  'Dispositivos'  AS `Tipo`,
  left (`tc_devices`.`created`, 7) AS `Fecha`,
  `CAPITALIZE`(monthname(`tc_devices`.`created`)) AS `Mes`,
  count(0) AS `Total`
from
  (`tc_devices`
  left join `tc_accounts` on ((`tc_accounts`.`id` = `tc_devices`.`account`)))
where
  ((`tc_devices`.`created` < last_day((curdate() + interval 1 day))) and
  (year (`tc_devices`.`created`) = :varAno) and
  (`tc_accounts`.`test` = 0) and `tc_devices`.`appname` = :varappname)
group by
  `tc_devices`.`appname`,
  left (`tc_devices`.`created`, 7)
union
select
  `tc_users`.`appname` AS `appname`,
  'Usuarios'  AS `Tipo`,
  left (`tc_users`.`created`, 7) AS `Fecha`,
  `CAPITALIZE`(monthname(`tc_users`.`created`)) AS `Mes`,
  count(0) AS `Total`
from
  (`tc_users`
  left join `tc_accounts` on ((`tc_accounts`.`id` = `tc_users`.`account`)))
where
  ((`tc_users`.`created` < last_day((curdate() + interval 1 day))) and
  (year (`tc_users`.`created`) = :varAno) and
  (`tc_users`.`type` <> '') and
  (`tc_accounts`.`test` = 0) and `tc_users`.`appname` = :varappname)
group by
  `tc_users`.`appname`,
  left (`tc_users`.`created`, 7)
union
select
  `tc_payments_view`.`appname` AS `appname`,
  'Renovaciones'  AS `Tipo`,
  left (`tc_payments_view`.`issued`, 7) AS `Fecha`,
  `CAPITALIZE`(monthname(`tc_payments_view`.`issued`)) AS `Mes`,
  count(0) AS `Total`
from
  `tc_payments_view`
where
  ((`tc_payments_view`.`issued` < last_day((curdate() + interval 1 day))) and
  (year (`tc_payments_view`.`issued`) = :varano) and
  (`tc_payments_view`.`notes` like '%renovación%') and
  (`tc_payments_view`.`test` = 0) and `tc_payments_view`.`appname` = :varappname)
group by
  `tc_payments_view`.`appname`,
  left (`tc_payments_view`.`issued`, 7);
  COMMIT;

DROP TEMPORARY TABLE IF EXISTS tc_statistics_total;
CREATE TEMPORARY TABLE tc_statistics_total AS
select
  `tc_accounts`.`appname` AS `appname`,
  'Cuentas'  AS `Tipo`,
  `tc_accounts`.`user` AS `Usuario`,
  left (`tc_accounts`.`created`, 7) AS `Fecha`,
  `CAPITALIZE`(monthname(`tc_accounts`.`created`)) AS `Mes`,
  count(0) AS `Total`
from
  `tc_accounts`
where
  ((`tc_accounts`.`created` < last_day((curdate() + interval 1 day))) and
  (year (`tc_accounts`.`created`) = :varAno) and
  (`tc_accounts`.`test` = 0) and `tc_accounts`.`appname` = :varappname and `tc_accounts`.`user` = :varuser)
group by
  `tc_accounts`.`appname`,
  left (`tc_accounts`.`created`, 7),
  `tc_accounts`.`user`  
union
select
  `tc_devices`.`appname` AS `appname`,
  'Dispositivos'  AS `Tipo`,
  `tc_devices`.`user` AS `Usuario`,
  left (`tc_devices`.`created`, 7) AS `Fecha`,
  `CAPITALIZE`(monthname(`tc_devices`.`created`)) AS `Mes`,
  count(0) AS `Total`
from
  (`tc_devices`
  left join `tc_accounts` on ((`tc_accounts`.`id` = `tc_devices`.`account`)))
where
  ((`tc_devices`.`created` < last_day((curdate() + interval 1 day))) and
  (year (`tc_devices`.`created`) = :varAno) and
  (`tc_accounts`.`test` = 0) and `tc_devices`.`appname` = :varappname and `tc_devices`.`user` = :varuser)
group by
  `tc_devices`.`appname`,
  left (`tc_devices`.`created`, 7),
  `tc_devices`.`user`  
union
select
  `tc_users`.`appname` AS `appname`,
  'Usuarios'  AS `Tipo`,
  `tc_users`.`user` AS `Usuario`,
  left (`tc_users`.`created`, 7) AS `Fecha`,
  `CAPITALIZE`(monthname(`tc_users`.`created`)) AS `Mes`,
  count(0) AS `Total`
from
  (`tc_users`
  left join `tc_accounts` on ((`tc_accounts`.`id` = `tc_users`.`account`)))
where
  ((`tc_users`.`created` < last_day((curdate() + interval 1 day))) and
  (year (`tc_users`.`created`) = :varAno) and
  (`tc_users`.`type` <> '') and
  (`tc_accounts`.`test` = 0) and `tc_users`.`appname` = :varappname and `tc_users`.`user` = :varuser)
group by
  `tc_users`.`appname`,
  left (`tc_users`.`created`, 7),
  `tc_users`.`user`  
union
select
  `tc_payments_view`.`appname` AS `appname`,
  'Renovaciones'  AS `Tipo`,
  `tc_payments_view`.`userowner` AS `Usuario`,
  left (`tc_payments_view`.`issued`, 7) AS `Fecha`,
  `CAPITALIZE`(monthname(`tc_payments_view`.`issued`)) AS `Mes`,
  count(0) AS `Total`
from
  `tc_payments_view`
where
  ((`tc_payments_view`.`issued` < last_day((curdate() + interval 1 day))) and
  (year (`tc_payments_view`.`issued`) = :varano) and
  (`tc_payments_view`.`notes` like '%renovación%') and
  (`tc_payments_view`.`test` = 0) and `tc_payments_view`.`appname` = :varappname and `tc_payments_view`.`userowner` = :varuser)
group by
  `tc_payments_view`.`appname`,
  left (`tc_payments_view`.`issued`, 7),
  `tc_payments_view`.`userowner`;
  COMMIT;