--
-- DROP TABLE
--
DROP VIEW IF EXISTS `flusso ambulatorio`;
DROP VIEW IF EXISTS `flusso dh`;
DROP VIEW IF EXISTS `flusso dh completo`;
DROP VIEW IF EXISTS `flusso ricovero`;
DROP VIEW IF EXISTS `flusso ticket`;
DROP VIEW IF EXISTS `numero stati ticket per operatore`;
DROP VIEW IF EXISTS `ticket prelevati e accettati`;
DROP VIEW IF EXISTS `totale stati ticket per data e modalita`;



-- Create syntax for VIEW 'flusso ambulatorio'
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `flusso ambulatorio`
AS SELECT
   `pa`.`id` AS `IdPrestazione`,
   `pa`.`modalita` AS `IdModalita`,
   `pa`.`codice_agenda` AS `IdAgenda`,date_format(`pa`.`data`,'%Y-%m-%d') AS `Data`,max((case when (`pa`.`id_infermiere` > 0) then `pa`.`id_infermiere` end)) AS `IdInfermiere`,max((case when (`pa`.`id_medico` > 0) then `pa`.`id_medico` end)) AS `IdMedico`,
   `acc`.`ora_accettazione` AS `Accettato`,
   `pa`.`ora_presa_carico` AS `Chiamato`,
   `pa`.`inizio_prestazione` AS `InizioPrestazione`,
   `pa`.`fine_prestazione` AS `FinePrestazione`
FROM (`ec_prestazione_ambulatorio` `pa` join `ec_accettazione` `acc` on((`acc`.`id` = `pa`.`id_accettazione`))) group by `pa`.`id`;

-- Create syntax for VIEW 'flusso dh'
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `flusso dh`
AS SELECT
   `pd`.`id` AS `IdPrestazione`,
   `pd`.`modalita` AS `IdModalita`,date_format(`pd`.`data`,'%Y-%m-%d') AS `Data`,max((case when (`pd`.`id_infermiere` > 0) then `pd`.`id_infermiere` end)) AS `IdInfermiere`,max((case when (`pd`.`id_medico` > 0) then `pd`.`id_medico` end)) AS `IdMedico`,
   `pd`.`inizio_prelievo` AS `InizioPrelievo`,
   `pd`.`fine_prelievo` AS `FinePrelievo`,
   `pd`.`inizio_prestazione` AS `InizioPrestazione`,
   `pd`.`fine_prestazione` AS `FinePrestazione`
FROM (`ec_prestazione_day_hospital` `pd` join `ec_accettazione` `acc` on((`acc`.`id` = `pd`.`id_accettazione`))) group by `pd`.`id`;

-- Create syntax for VIEW 'flusso dh completo'
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `flusso dh completo`
AS SELECT
   `pd`.`id` AS `IdPrestazione`,
   `t`.`ticket_id` AS `IdTicket`,
   `pd`.`modalita` AS `IdModalita`,date_format(`pd`.`data`,'%Y-%m-%d') AS `Data`,max((case when (`t`.`utente_id` > 0) then `t`.`utente_id` end)) AS `IdOperatore`,max((case when (`t`.`status` = 3) then `t`.`data` end)) AS `Prelevato`,max((case when (`t`.`status` = 4) then `t`.`data` end)) AS `Chiamato`,max((case when (`t`.`status` = 7) then `t`.`data` end)) AS `Presente`,max((case when (`t`.`status` = 6) then `t`.`data` end)) AS `Assente`,max((case when (`pd`.`id_infermiere` > 0) then `pd`.`id_infermiere` end)) AS `IdInfermiere`,max((case when (`pd`.`id_medico` > 0) then `pd`.`id_medico` end)) AS `IdMedico`,
   `pd`.`inizio_prelievo` AS `InizioPrelievo`,
   `pd`.`fine_prelievo` AS `FinePrelievo`,
   `pd`.`inizio_prestazione` AS `InizioPrestazione`,
   `pd`.`fine_prestazione` AS `FinePrestazione`
FROM (((`ec_tracking_status_ticket` `t` join `ec_ticket_totem` `tt` on((`t`.`ticket_id` = `tt`.`id`))) join `ec_accettazione` `acc` on((`tt`.`id` = `acc`.`id_totem`))) join `ec_prestazione_day_hospital` `pd` on((`pd`.`id` = `acc`.`id_prestazione`))) where (`tt`.`id_modalita` = 3) group by `pd`.`id`;

-- Create syntax for VIEW 'flusso ricovero'
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `flusso ricovero`
AS SELECT
   `pr`.`id` AS `IdRicovero`,
   `pr`.`modalita` AS `IdModalita`,date_format(`pr`.`data`,'%Y-%m-%d') AS `Data`,max((case when (`pr`.`id_infermiere` > 0) then `pr`.`id_infermiere` end)) AS `IdInfermiere`,
   `acc`.`ora_accettazione` AS `Accettato`,
   `pr`.`ora_presa_carico` AS `Chiamato`,
   `pr`.`inizio_prestazione` AS `InizioRicovero`,
   `pr`.`fine_prestazione` AS `FineRicovero`
FROM (`ec_prestazione_ricovero` `pr` join `ec_accettazione` `acc` on((`acc`.`id` = `pr`.`id_accettazione`))) group by `pr`.`id`;

-- Create syntax for VIEW 'flusso ticket'
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `flusso ticket`
AS SELECT
   `t`.`ticket_id` AS `IdTicket`,
   `tt`.`id_modalita` AS `IdModalita`,
   `tt`.`id_agenda_htsan` AS `IdAgenda`,date_format(`t`.`data`,'%Y-%m-%d') AS `Data`,max((case when (`t`.`utente_id` > 0) then `t`.`utente_id` end)) AS `IdOperatore`,max((case when (`t`.`status` = 3) then `t`.`data` end)) AS `Prelevato`,max((case when (`t`.`status` = 4) then `t`.`data` end)) AS `Chiamato`,max((case when (`t`.`status` = 5) then `t`.`data` end)) AS `Presente`,max((case when (`t`.`status` = 6) then `t`.`data` end)) AS `Assente`,max((case when (`t`.`status` = 7) then `t`.`data` end)) AS `Accettato`
FROM (`ec_tracking_status_ticket` `t` join `ec_ticket_totem` `tt` on((`t`.`ticket_id` = `tt`.`id`))) group by `t`.`ticket_id`;

-- Create syntax for VIEW 'numero stati ticket per operatore'
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `numero stati ticket per operatore`
AS SELECT
   `t`.`utente_id` AS `id_operatore`,date_format(`t`.`data`,'%Y-%m-%d') AS `DataCorrente`,count(`t`.`status`) AS `N_Totali`,count((case when (`t`.`status` = 4) then `t`.`status` end)) AS `N_Chiamati`,count((case when (`t`.`status` = 5) then `t`.`status` end)) AS `N_Presenti`,count((case when (`t`.`status` = 6) then `t`.`status` end)) AS `N_Assenti`,count((case when (`t`.`status` = 7) then `t`.`status` end)) AS `N_Accettati`
FROM (`ec_tracking_status_ticket` `t` join `ec_ticket_totem` `tt` on((`t`.`ticket_id` = `tt`.`id`))) where (date_format(`t`.`data`,'%Y-%m-%d') = curdate()) group by date_format(`t`.`data`,'%Y-%m-%d'),`t`.`utente_id` having (`t`.`utente_id` > 0);

-- Create syntax for VIEW 'ticket prelevati e accettati'
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ticket prelevati e accettati`
AS SELECT
   `ett`.`id` AS `id_ticket`,
   `ett`.`id_modalita` AS `id_modalita`,
   `ett`.`codice_ticket` AS `codice_ticket`,
   `ett`.`data` AS `data_prelievo_ticket`,
   `ea`.`id_operatore` AS `id_operatore`,
   `ea`.`data_inserimento` AS `data_accettazione`,
   `ea`.`ora_accettazione` AS `ora_accettazione`
FROM (`ec_ticket_totem` `ett` left join `ec_accettazione` `ea` on((`ett`.`id` = `ea`.`id_totem`)));

-- Create syntax for VIEW 'totale stati ticket per data e modalita'
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `totale stati ticket per data e modalita`
AS SELECT
   `tt`.`id_modalita` AS `IdModalita`,date_format(`t`.`data`,'%Y-%m-%d') AS `DataCorrente`,count(`t`.`status`) AS `N_Totali`,count((case when (`t`.`status` = 3) then `t`.`status` end)) AS `N_Prelevati`,count((case when (`t`.`status` = 4) then `t`.`status` end)) AS `N_Chiamati`,count((case when (`t`.`status` = 5) then `t`.`status` end)) AS `N_Presenti`,count((case when (`t`.`status` = 6) then `t`.`status` end)) AS `N_Assenti`,count((case when (`t`.`status` = 7) then `t`.`status` end)) AS `N_Accettati`
FROM (`ec_tracking_status_ticket` `t` join `ec_ticket_totem` `tt` on((`t`.`ticket_id` = `tt`.`id`))) where (date_format(`t`.`data`,'%Y-%m-%d') = curdate()) group by date_format(`t`.`data`,'%Y-%m-%d'),`tt`.`id_modalita`;