io.vertigo.planning.studio.agenda.dao.agenda_tasks.ksp Maven / Gradle / Ivy
The newest version!
package io.vertigo.planning.agenda
create Task TkGetTrancheHorairesDisponibleByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT trh.*
FROM tranche_horaire trh
WHERE trh.age_id in ( #ageIds.rownum# )
AND trh.date_locale BETWEEN #startDate# AND #endDate#
AND trh.date_locale >= #displayDayMin#
AND (trh.date_locale > #displayDayMin# OR trh.minutes_Debut > #displayMinutesMin#)
AND trh.instant_Publication <= #now#
AND EXISTS (
SELECT 1 FROM creneau cre
WHERE cre.trh_id = trh.trh_id
AND cre.rec_id is null)
order by trh.date_locale, trh.minutes_Debut;
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
in displayDayMin {domain : DoPLocalDate cardinality: "1" }
in displayMinutesMin {domain : DoPHeureMinute cardinality: "1" }
out trancheHoraires {domain : DoDtTrancheHoraire cardinality: "*" }
}
create Task TkCloseTrancheHoraireByTrhId {
className : "io.vertigo.basics.task.TaskEngineProc"
request : "
delete from creneau cre where cre.trh_id in ( #trhIds.rownum# );
update tranche_horaire trh set nb_guichet = 0, instant_Publication = null
where trh.trh_id in ( #trhIds.rownum# );
"
in trhIds {domain : DoPId cardinality: "*" }
}
create Task TkGetTrancheHorairesFermeesByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT trh.*
FROM tranche_horaire trh
WHERE trh.age_id in ( #ageIds.rownum# )
AND trh.date_locale BETWEEN #startDate# AND #endDate#
AND trh.nb_guichet = 0
order by trh.date_locale, trh.minutes_Debut;
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
out trancheHoraires {domain : DoDtTrancheHoraire cardinality: "*" }
}
create Task TkGetDatePremiereDisponibiliteByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT trh.date_locale
FROM creneau cre
join tranche_horaire trh on cre.trh_id = trh.trh_id
WHERE trh.age_id in ( #ageIds.rownum# )
AND trh.date_locale BETWEEN #startDate# AND #endDate#
AND trh.date_locale >= #displayDayMin#::Date
AND (trh.date_locale > #displayDayMin#::Date OR trh.minutes_Debut > #displayMinutesMin#)
AND trh.instant_publication <= #now#
AND cre.rec_id is null
order by trh.date_locale
limit 1;
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
in displayDayMin {domain : DoPLocalDate cardinality: "1" }
in displayMinutesMin {domain : DoPHeureMinute cardinality: "1" }
out premiereDisponibilite {domain : DoPLocalDate cardinality: "?" }
}
create Task TkSynchroGetTrancheHorairesByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select * from (
select
trh.trh_id,
trh.age_id,
trh.date_locale,
trh.minutes_debut,
trh.minutes_fin,
trh.instant_publication,
sum( case when cre.rec_id is null then 1 else 0 end) as nb_guichet
from creneau cre
join tranche_horaire trh on cre.trh_id = trh.trh_id
where trh.age_id in ( #ageIds.rownum# )
AND trh.date_locale >= #now#::Date
AND trh.instant_publication <= #now#
group by trh.trh_id, trh.date_locale, trh.minutes_debut
order by trh.date_locale, trh.minutes_debut
) as dispo
where dispo.nb_guichet > 0
"
in ageIds {domain : DoPId cardinality: "*" }
in now {domain : DoPInstant cardinality: "1" }
out trancheHoraires {domain : DoDtTrancheHoraire cardinality: "*" }
}
create Task TkSynchroGetTrancheHorairesByAgeIdsAndDates {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select * from (
select
trh.trh_id,
trh.age_id,
trh.date_locale,
trh.minutes_debut,
trh.instant_publication,
sum( case when cre.rec_id is null then 1 else 0 end) as nb_guichet
from creneau cre
join tranche_horaire trh on cre.trh_id = trh.trh_id
where trh.age_id in ( #ageIds.rownum# )
AND trh.date_locale in (#localDates.rownum#)
AND trh.instant_publication <= #now#
group by trh.trh_id, trh.date_locale, trh.minutes_debut
order by trh.date_locale, trh.minutes_debut
) as dispo
where dispo.nb_guichet > 0
"
in ageIds {domain : DoPId cardinality: "*" }
in localDates {domain : DoPLocalDate cardinality: "*" }
in now {domain : DoPInstant cardinality: "1" }
out trancheHoraires {domain : DoDtTrancheHoraire cardinality: "*" }
}
create Task TkGetDateDernierePublicationByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT max(trh.date_locale)
FROM tranche_horaire trh
WHERE trh.age_id in ( #ageIds.rownum# )
AND trh.date_locale BETWEEN #now#::Date AND (#now#::Date + interval '1 year')
AND trh.instant_publication <= #now#;
"
in ageIds {domain : DoPId cardinality: "*" }
in now {domain : DoPInstant cardinality: "1" }
out premiereDisponibilite {domain : DoPLocalDate cardinality: "?" }
}
create Task TkGetPrecedentePublicationByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT
min(date_locale) as date_min,
max(date_locale) as date_max,
instant_publication
FROM tranche_horaire trh1
WHERE trh1.age_id in ( #ageIds.rownum# )
AND trh1.date_locale BETWEEN (#now#::Date - interval '1 year') AND (#now#::Date + interval '1 year')
AND trh1.instant_publication IN (
SELECT max(trh2.instant_publication)
FROM tranche_horaire trh2
WHERE trh2.age_id in ( #ageIds.rownum# )
AND trh2.date_locale BETWEEN (#now#::Date - interval '1 year') AND (#now#::Date + interval '1 year')
AND trh2.instant_publication <= #now#
)
GROUP BY trh1.instant_publication;
"
in ageIds {domain : DoPId cardinality: "*" }
in now {domain : DoPInstant cardinality: "1" }
out publicationRange {domain : DoDtPublicationRange cardinality: "?" }
}
create Task TkGetProchainePublicationByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT
min(date_locale) as date_min,
max(date_locale) as date_max,
instant_publication
FROM tranche_horaire trh1
WHERE trh1.age_id in ( #ageIds.rownum# )
AND trh1.date_locale BETWEEN #now#::Date AND (#now#::Date + interval '1 year')
AND trh1.instant_publication IN (
SELECT min(trh2.instant_publication)
FROM tranche_horaire trh2
WHERE trh2.age_id in ( #ageIds.rownum# )
AND trh2.date_locale BETWEEN #now#::Date AND (#now#::Date + interval '1 year')
AND trh2.instant_publication > #now#
)
GROUP BY trh1.instant_publication;
"
in ageIds {domain : DoPId cardinality: "*" }
in now {domain : DoPInstant cardinality: "1" }
out publicationRange {domain : DoDtPublicationRange cardinality: "?" }
}
create Task TkGetPlageHoraireDisplayByAgeId {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select
plh.plh_Id as plh_Id,
plh.age_Id as age_Id,
age.nom as age_nom,
plh.date_Locale as date_Locale,
plh.minutes_Debut as minutes_Debut,
plh.minutes_Fin as minutes_Fin,
plh.nb_Guichet as nb_Guichet,
sum(((trh.instant_Publication is null)::int) * trh.nb_Guichet) as nb_Non_Publie,
sum(COALESCE(((trh.instant_Publication > #now#)::int),0) * trh.nb_Guichet) as nb_Planifie,
sum(COALESCE(((trh.instant_Publication <= #now#)::int),0) * trh.nb_Guichet) as nb_Publie,
sum(trh.nb_Guichet) as nb_Total,
min(trh.instant_Publication) as instant_Publication,
sum(COALESCE(res.nb_Reserve_Non_Publie,0)) as nb_Reserve_Non_Publie,
sum(COALESCE(res.nb_Reserve,0)) as nb_Reserve
from plage_horaire plh
join agenda age on age.age_id = plh.age_id
join tranche_horaire trh on trh.plh_id = plh.plh_id
left join (
SELECT trh.trh_Id as trh_Id,
sum (case when trh.instant_Publication <= #now# and trh.nb_Guichet > 0 then 0 else 1 end) as nb_Reserve_Non_Publie,
count(1) as nb_Reserve
FROM reservation_creneau res
join tranche_horaire trh on trh.age_id = res.age_id
AND res.date_locale = trh.date_locale
AND res.minutes_debut >= trh.minutes_Debut
AND res.minutes_debut < trh.minutes_Fin
WHERE res.age_id = #ageId#
AND res.date_locale BETWEEN #startDate# AND #endDate#
GROUP BY trh.trh_id) as res on res.trh_id = trh.trh_id
WHERE plh.age_id = #ageId#
AND plh.date_locale BETWEEN #startDate# AND #endDate#
GROUP BY plh.plh_Id, plh.age_Id, age.nom, plh.date_Locale, plh.minutes_Debut, plh.minutes_Fin, plh.nb_Guichet;
"
in ageId {domain : DoPId cardinality: "1" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
out plageHoraires {domain : DoDtPlageHoraireDisplay cardinality: "*" }
}
create Task TkGetPlageHoraireDisplayByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select
plh.plh_Id as plh_Id,
plh.age_Id as age_Id,
age.nom as age_nom,
plh.date_Locale as date_Locale,
plh.minutes_Debut as minutes_Debut,
plh.minutes_Fin as minutes_Fin,
plh.nb_Guichet as nb_Guichet,
sum(((trh.instant_Publication is null)::int) * trh.nb_Guichet) as nb_Non_Publie,
sum(COALESCE(((trh.instant_Publication > #now#)::int),0) * trh.nb_Guichet) as nb_Planifie,
sum(COALESCE(((trh.instant_Publication <= #now#)::int),0) * trh.nb_Guichet) as nb_Publie,
sum(trh.nb_Guichet) as nb_Total,
min(trh.instant_Publication) as instant_Publication,
sum(COALESCE(res.nb_Reserve_Non_Publie,0)) as nb_Reserve_Non_Publie,
sum(COALESCE(res.nb_Reserve,0)) as nb_Reserve
from plage_horaire plh
join tranche_horaire trh on trh.plh_id = plh.plh_id
join agenda age on age.age_id = plh.age_id
left join (
SELECT trh.trh_Id as trh_Id, res.age_id as age_id,
sum (case when trh.instant_Publication <= #now# and trh.nb_Guichet > 0 then 0 else 1 end) as nb_Reserve_Non_Publie,
count(1) as nb_Reserve
FROM reservation_creneau res
join tranche_horaire trh on trh.age_id = res.age_id
AND res.date_locale = trh.date_locale
AND res.minutes_debut >= trh.minutes_Debut
AND res.minutes_debut < trh.minutes_Fin
WHERE res.age_id in ( #ageIds.rownum# )
AND res.date_locale BETWEEN #startDate# AND #endDate#
GROUP BY trh.trh_id, res.age_id) as res on res.trh_id = trh.trh_id and res.age_id = plh.age_id
WHERE plh.age_id in ( #ageIds.rownum# )
AND plh.date_locale BETWEEN #startDate# AND #endDate#
GROUP BY plh.plh_Id, plh.age_Id, age.nom, plh.date_Locale, plh.minutes_Debut, plh.minutes_Fin, plh.nb_Guichet;
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
out plageHoraires {domain : DoDtPlageHoraireDisplay cardinality: "*" }
}
create Task TkGetPlageHoraireDisplayByPlhId {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select
plh.plh_Id as plh_Id,
plh.age_Id as age_Id,
age.nom as age_nom,
plh.date_Locale as date_Locale,
plh.minutes_Debut as minutes_Debut,
plh.minutes_Fin as minutes_Fin,
plh.nb_Guichet as nb_Guichet,
sum(((trh.instant_Publication is null)::int) * trh.nb_Guichet) as nb_Non_Publie,
sum(COALESCE(((trh.instant_Publication > #now#)::int),0) * trh.nb_Guichet) as nb_Planifie,
sum(COALESCE(((trh.instant_Publication <= #now#)::int),0) * trh.nb_Guichet) as nb_Publie,
sum(trh.nb_Guichet) as nb_Total,
min(trh.instant_Publication) as instant_Publication,
sum(COALESCE(res.nb_Reserve_Non_Publie,0)) as nb_Reserve_Non_Publie,
sum(COALESCE(res.nb_Reserve,0)) as nb_Reserve
from plage_horaire plh
join agenda age on age.age_id = plh.age_id
join tranche_horaire trh on trh.plh_id = plh.plh_id
left join (
SELECT trh.trh_Id as trh_Id,
sum (case when trh.instant_Publication <= #now# and trh.nb_Guichet > 0 then 0 else 1 end) as nb_Reserve_Non_Publie,
count(1) as nb_Reserve
FROM reservation_creneau res
join tranche_horaire trh on trh.age_id = res.age_id
AND res.date_locale = trh.date_locale
AND res.minutes_debut >= trh.minutes_Debut
AND res.minutes_debut < trh.minutes_Fin
WHERE trh.plh_id = #plhId#
GROUP BY trh.trh_id) as res on res.trh_id = trh.trh_id
WHERE plh.plh_id = #plhId#
GROUP BY plh.plh_Id, plh.age_Id, age.nom, plh.date_Locale, plh.minutes_Debut, plh.minutes_Fin, plh.nb_Guichet;
"
in plhId {domain : DoPId cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
out plageHoraires {domain : DoDtPlageHoraireDisplay cardinality: "1" }
}
create Task TkGetTrancheHoraireDisplayByPlhId {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select
trh.trh_Id as trh_Id,
trh.date_Locale as date_Locale,
trh.minutes_Debut as minutes_Debut,
trh.minutes_Fin as minutes_Fin,
trh.nb_Guichet as nb_Guichet,
trh.instant_Publication,
(CASE
WHEN (trh.instant_Publication is null) THEN 'nonPublie'
WHEN (trh.instant_Publication > #now#) THEN 'planifie'
WHEN (trh.instant_Publication <= #now#) THEN 'publie'
END) as etat_publication,
COALESCE(res.nb_Reserve_Non_Publie,0) as nb_Reserve_Non_Publie,
COALESCE(res.nb_Reserve,0) as nb_Reserve
from tranche_horaire trh
left join (
SELECT trh.trh_Id as trh_Id,
sum (case when trh.instant_Publication <= #now# and trh.nb_Guichet > 0 then 0 else 1 end) as nb_Reserve_Non_Publie,
count(1) as nb_Reserve
FROM reservation_creneau res
join tranche_horaire trh on trh.age_id = res.age_id
AND res.date_locale = trh.date_locale
AND res.minutes_debut >= trh.minutes_Debut
AND res.minutes_debut < trh.minutes_Fin
WHERE trh.plh_id = #plhId#
GROUP BY trh.trh_id) as res on res.trh_id = trh.trh_id
WHERE trh.plh_id = #plhId#
ORDER BY trh.minutes_Debut;
"
in plhId {domain : DoPId cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
out trancheHoraires {domain : DoDtTrancheHoraireDisplay cardinality: "*" }
}
create Task TkGetDateDisponibleDisplayByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select date_Locale,
sum(nb_Non_Publie) as nb_Non_Publie,
sum(nb_Planifie) as nb_Planifie,
sum(nb_Publie) as nb_Publie,
sum(nb_Reserve) as nb_Reserve,
sum(nb_Total) as nb_Total,
min(instant_Publication) as instant_Publication
from (select
plh.date_Locale as date_Locale,
sum(((trh.instant_Publication is null)::int) * trh.nb_Guichet) as nb_Non_Publie,
sum(COALESCE(((trh.instant_Publication > #now#)::int),0) * trh.nb_Guichet) as nb_Planifie,
sum(COALESCE(((trh.instant_Publication <= #now#)::int),0) * trh.nb_Guichet) as nb_Publie,
sum(COALESCE(res.nb_Reserve,0)) as nb_Reserve,
sum(trh.nb_Guichet) as nb_Total,
min(trh.instant_Publication) as instant_Publication
from plage_horaire plh
join tranche_horaire trh on trh.plh_id = plh.plh_id
left join (
SELECT trh.trh_Id as trh_Id, res.age_id as age_id,
count(1) as nb_Reserve
FROM reservation_creneau res
join tranche_horaire trh on trh.age_id = res.age_id
AND res.date_locale = trh.date_locale
AND res.minutes_debut >= trh.minutes_Debut
AND res.minutes_debut < trh.minutes_Fin
WHERE res.age_id in ( #ageIds.rownum# )
GROUP BY trh.trh_id, res.age_id) as res on res.trh_id = trh.trh_id and res.age_id = plh.age_id
WHERE plh.age_id in ( #ageIds.rownum# )
AND plh.date_locale BETWEEN #startDate# AND #endDate#
GROUP BY plh.date_Locale, plh.nb_Guichet) as sub
GROUP BY date_Locale
ORDER BY date_Locale;
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
out plageHoraires {domain : DoDtDateDisponibleDisplay cardinality: "*" }
}
create Task TkGetTrancheHoraireDisplayByDate {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select
trh.trh_Id as trh_Id,
trh.date_Locale as date_Locale,
trh.minutes_Debut as minutes_Debut,
trh.minutes_Fin as minutes_Fin,
trh.nb_Guichet as nb_Guichet,
trh.instant_Publication,
(CASE
WHEN (trh.instant_Publication is null) THEN 'nonPublie'
WHEN (trh.instant_Publication > #now#) THEN 'planifie'
WHEN (trh.instant_Publication <= #now#) THEN 'publie'
END) as etat_publication,
COALESCE(res.nb_Reserve_Non_Publie,0) as nb_Reserve_Non_Publie,
COALESCE(res.nb_Reserve,0) as nb_Reserve
from tranche_horaire trh
left join (
SELECT trh2.trh_Id as trh_Id, trh2.age_id as age_id,
sum (case when trh2.instant_Publication <= #now# and trh2.nb_Guichet > 0 then 0 else 1 end) as nb_Reserve_Non_Publie,
count(1) as nb_Reserve
FROM reservation_creneau res
join tranche_horaire trh2 on trh2.age_id = res.age_id
AND res.date_locale = trh2.date_locale
AND res.minutes_debut >= trh2.minutes_Debut
AND res.minutes_debut < trh2.minutes_Fin
WHERE trh2.age_id in ( #ageIds.rownum# ) and trh2.date_Locale = #dateLocale#
GROUP BY trh2.trh_id, trh2.age_id) as res on res.trh_id = trh.trh_id and res.age_id = trh.age_id
WHERE trh.age_id in ( #ageIds.rownum# ) and trh.date_locale = #dateLocale#
ORDER BY trh.minutes_Debut;
"
in ageIds {domain : DoPId cardinality: "*" }
in dateLocale {domain : DoPLocalDate cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
out trancheHoraires {domain : DoDtTrancheHoraireDisplay cardinality: "*" }
}
create Task TkCountUnlinkReservationPerXminByAgeId {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT
res.date_Locale as date_Locale,
(floor(res.minutes_Debut/<%=minuteAgg%>))*<%=minuteAgg%> as minutes_Debut,
(floor(res.minutes_Debut/<%=minuteAgg%>)+1)*<%=minuteAgg%> as minutes_Fin,
count(1) as nb_Reserve
FROM reservation_creneau res
WHERE res.age_id in ( #ageIds.rownum# )
AND res.date_locale BETWEEN #startDate# AND #endDate#
AND not exists (SELECT 1 FROM tranche_horaire trh
WHERE trh.age_id = res.age_id
AND res.date_locale = trh.date_locale
AND res.minutes_debut >= trh.minutes_Debut
AND res.minutes_debut < trh.minutes_Fin)
GROUP BY res.date_Locale, floor(res.minutes_Debut/<%=minuteAgg%>);
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in minuteAgg {domain : DoPMinute cardinality: "1" }
out plageHoraires {domain : DoDtPlageHoraireDisplay cardinality: "*" }
}
create Task TkGetDefaultPlageHoraireByAgeId {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select
1 as jour_de_semaine,
plh.minutes_Debut as minutes_Debut,
plh.minutes_Fin as minutes_Fin,
plh.nb_Guichet as nb_Guichet
from plage_horaire plh
WHERE plh.age_id = #ageId#
AND plh.date_locale BETWEEN #startDate# AND #endDate#;
"
in ageId {domain : DoPId cardinality: "1" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
out plageHoraires {domain : DoDtDefaultPlageHoraire cardinality: "*" }
}
create Task TkGetExistsConflictingPlageHoraire {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
select plh.*
from plage_horaire plh
WHERE plh.age_id in ( #ageIds.rownum# )
AND plh.date_locale = #dateLocale#
AND plh.minutes_Debut < #heureFin# AND plh.minutes_Fin > #heureDebut#
LIMIT 1;
"
in ageIds {domain : DoPId cardinality: "*" }
in dateLocale {domain : DoPLocalDate cardinality: "1" }
in heureDebut {domain : DoPHeureMinute cardinality: "1" }
in heureFin {domain : DoPHeureMinute cardinality: "1" }
out plageHoraires {domain : DoDtPlageHoraire cardinality: "?" }
}
create Task TkGetFirstLocalDatesFreeOfPlageHorairePerDayOfWeek {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
WITH plhTo as --récupère la liste jour déjà renseigné en collision avec celles de la semaine à copier
(SELECT distinct plhTo.date_locale, plhFrom.dow, plhTo.woe,
row_number() over (partition by plhFrom.dow order by plhTo.date_locale asc) as date_per_dow_order
FROM (SELECT distinct age_id, date_locale, extract(isodow from date_locale) dow, floor(extract(JULIAN from date_locale)/7) woe
FROM plage_horaire
WHERE age_id in ( #ageIds.rownum# )
AND date_locale between #dateLocaleFromDebut# AND #dateLocaleFromFin#) as plhFrom
join (SELECT distinct age_id, date_locale, extract(isodow from date_locale) dow, floor(extract(JULIAN from date_locale)/7) woe --on ne garde que les DayOfWeek de la semaine source
FROM plage_horaire plh
WHERE age_id in ( #ageIds.rownum# )
AND date_locale between #dateLocaleToDebut# AND #dateLocaleToFin#) as plhTo
on plhTo.age_id = plhFrom.age_id and plhTo.dow = plhFrom.dow)
SELECT *
FROM (
SELECT (firstDate.date_locale+7-firstDate.dow::integer+1) firstFreeDate, row_number() over (partition by firstDate.dow order by firstDate.date_locale asc)
FROM plhTo as firstDate
WHERE not exists (select 1 from plhTo as secondDate where secondDate.woe = firstDate.woe+1) --on filtre les dates dont la semaine suivante est libre
) firstDates
WHERE firstDates.row_number = 1
order by firstDates.firstFreeDate;
"
in ageIds {domain : DoPId cardinality: "*" }
in dateLocaleFromDebut {domain : DoPLocalDate cardinality: "1" }
in dateLocaleFromFin {domain : DoPLocalDate cardinality: "1" }
in dateLocaleToDebut {domain : DoPLocalDate cardinality: "1" }
in dateLocaleToFin {domain : DoPLocalDate cardinality: "1" }
out firstDatesPerDow {domain : DoPLocalDate cardinality: "*" }
}
create Task TkDeletePlageHoraireCascadeByPlhId {
className : "io.vertigo.basics.task.TaskEngineProc"
request : "
delete from creneau cre where cre.trh_id in (select trh.trh_id from tranche_horaire trh where trh.plh_id = #plhId#);
delete from tranche_horaire trh WHERE trh.plh_id = #plhId#;
delete from plage_horaire plh WHERE plh.plh_id = #plhId#;
"
in plhId {domain : DoPId cardinality: "1" }
}
create Task TkSelectCreneauForUpdateByTrhId {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT
cre.*
FROM CRENEAU cre
WHERE cre.TRH_ID = #trhId# and cre.rec_id is null
LIMIT 1 FOR UPDATE SKIP LOCKED
"
in trhId {domain : DoPId cardinality: "1" }
out creneau {domain : DoDtCreneau cardinality: "?" }
}
create Task TkSelectCreneauForUpdateByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT cre.*
FROM CRENEAU cre
join tranche_horaire trh on cre.trh_id = trh.trh_id
WHERE trh.age_id in ( #ageIds.rownum# )
AND trh.date_locale BETWEEN #startDate# AND #endDate#
AND trh.minutes_Debut BETWEEN #startMinutes# AND #endMinutes#
AND trh.instant_Publication <= #now#
and cre.rec_id is null
LIMIT 1 FOR UPDATE SKIP LOCKED
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in startMinutes {domain : DoPHeureMinute cardinality: "1" }
in endMinutes {domain : DoPHeureMinute cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
out creneau {domain : DoDtCreneau cardinality: "?" }
}
create Task TkSelectFreeCreneauByAgeId {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT
cre.*
FROM CRENEAU cre
join tranche_horaire trh on cre.trh_id = trh.trh_id
WHERE trh.age_id = #ageId#
AND trh.date_locale BETWEEN #startDate# AND #endDate#
AND trh.instant_Publication <= #now#
and cre.rec_id is null
"
in ageId {domain : DoPId cardinality: "1" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
out creneau {domain : DoDtCreneau cardinality: "*" }
}
create Task TkSelectFreeTrancheHorairesByAgeId {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT trh.*
FROM tranche_horaire trh
WHERE trh.age_id = #ageId#
AND trh.date_locale BETWEEN #startDate# AND #endDate#
AND trh.instant_Publication <= #now#
AND EXISTS (
SELECT 1 FROM creneau cre
WHERE cre.trh_id = trh.trh_id
AND cre.rec_id is null)
"
in ageId {domain : DoPId cardinality: "1" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
out trancheHoraires {domain : DoDtTrancheHoraire cardinality: "*" }
}
create Task TkPublishTrancheHoraireByAgeIds {
className : "io.vertigo.basics.task.TaskEngineProc"
request : "
UPDATE tranche_horaire trh
SET instant_publication = #instantPublication#
WHERE age_id in ( #ageIds.rownum# )
AND trh.date_locale BETWEEN #startDate# AND #endDate#
AND trh.nb_Guichet > 0
AND (trh.instant_Publication is null OR trh.instant_Publication > #now#);
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in now {domain : DoPInstant cardinality: "1" }
in instantPublication {domain : DoPInstant cardinality: "1" }
}
create Task TkCreateCreneauOfPublishedTrancheHoraireByAgeIds {
className : "io.vertigo.basics.task.TaskEngineProc"
request : "
WITH missing_count AS (
SELECT trh.trh_id, (trh.nb_guichet-count(cre.cre_id)) missing_Creneau
FROM tranche_horaire trh
left join creneau cre on cre.trh_id = trh.trh_id
WHERE age_id in ( #ageIds.rownum# )
AND trh.date_locale BETWEEN #startDate# AND #endDate#
AND instant_publication = #instantPublication#
GROUP BY trh.trh_id, trh.nb_guichet
)
INSERT INTO creneau (cre_id, trh_id/*, rec_id*/)
(SELECT nextval('SEQ_CRENEAU'), trh_id/*, null*/
FROM missing_count WHERE missing_Creneau>= 1
UNION ALL
SELECT nextval('SEQ_CRENEAU'), trh_id
FROM missing_count WHERE missing_Creneau>= 2
UNION ALL
SELECT nextval('SEQ_CRENEAU'), trh_id
FROM missing_count WHERE missing_Creneau>= 3
UNION ALL
SELECT nextval('SEQ_CRENEAU'), trh_id
FROM missing_count WHERE missing_Creneau>= 4
UNION ALL
SELECT nextval('SEQ_CRENEAU'), trh_id
FROM missing_count WHERE missing_Creneau>= 5
UNION ALL
SELECT nextval('SEQ_CRENEAU'), trh_id
FROM missing_count WHERE missing_Creneau>= 6
UNION ALL
SELECT nextval('SEQ_CRENEAU'), trh_id
FROM missing_count WHERE missing_Creneau>= 7
UNION ALL
SELECT nextval('SEQ_CRENEAU'), trh_id
FROM missing_count WHERE missing_Creneau>= 8
UNION ALL
SELECT nextval('SEQ_CRENEAU'), trh_id
FROM missing_count WHERE missing_Creneau>= 9
)
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
in instantPublication {domain : DoPInstant cardinality: "1" }
}
create Task TkBatchInsertTrancheHoraire {
className : "io.vertigo.basics.task.TaskEngineProcBatch"
request : "
insert into TRANCHE_HORAIRE (TRH_ID, AGE_ID, DATE_LOCALE, INSTANT_PUBLICATION, MINUTES_DEBUT, MINUTES_FIN, NB_GUICHET, PLH_ID)
values (nextval('SEQ_TRANCHE_HORAIRE'),
#dto.ageId#,
#dto.dateLocale#,
#dto.instantPublication#,
#dto.minutesDebut#,
#dto.minutesFin#,
#dto.nbGuichet#,
#dto.plhId#);
"
in dto {domain : DoDtTrancheHoraire cardinality:"*" }
}
/**
* Task liées aux réservations
**/
create Task TkGetLinkReservationAfterPublishByAgeIds {
className : "io.vertigo.basics.task.TaskEngineSelect"
request : "
SELECT
trh.trh_id as trh_id,
STRING_AGG( '' || cre.cre_id, ';') as cre_ids,
rec.rec_id, rec.instant_creation
FROM tranche_horaire trh
join creneau cre on trh.trh_id = cre.trh_id
join reservation_creneau rec on trh.age_id = rec.age_id
AND rec.date_locale = trh.date_locale
AND rec.minutes_debut >= trh.minutes_Debut AND rec.minutes_debut < trh.minutes_Fin
AND rec.rec_id not in (select cre2.rec_id from creneau cre2 where cre2.rec_id is not null and cre.trh_id = trh.trh_id )
WHERE trh.age_id in ( #ageIds.rownum# ) AND trh.date_locale BETWEEN #startDate# AND #endDate#
AND rec.age_id = trh.age_id AND rec.date_locale BETWEEN #startDate# AND #endDate#
AND cre.rec_id is null
GROUP BY trh.trh_id, rec.rec_id;
"
in ageIds {domain : DoPId cardinality: "*" }
in startDate {domain : DoPLocalDate cardinality: "1" }
in endDate {domain : DoPLocalDate cardinality: "1" }
out affectionReservation {domain : DoDtAffectionReservation cardinality: "*" }
}
create Task TkLinkCreneauToReservation {
className : "io.vertigo.basics.task.TaskEngineProcBatch"
request : "
UPDATE creneau
SET rec_Id = #affectionReservation.recId#
WHERE cre_id = #affectionReservation.creId#;
"
in affectionReservation {domain : DoDtAffectionReservation cardinality: "*" }
}
create Task TkSupprimerReservationsCreneau {
className : "io.vertigo.basics.task.TaskEngineProc"
request : "
UPDATE creneau
SET rec_id = null
WHERE rec_id in (#recIds.rownum#);
delete from reservation_creneau where rec_id in (#recIds.rownum#);
"
in recIds {domain : DoPId cardinality: "*" }
}
create Task TkReserverCreneaux {
className : "io.vertigo.basics.task.TaskEngineProcBatch"
request : "
UPDATE creneau
SET rec_id = #creneaux.0.rec_id#
WHERE cre_id = #creneaux.0.cre_id# and rec_id is null;
"
in creneaux {domain : DoDtCreneau cardinality: "*" }
out intSqlRowcount {domain : DoPNombre cardinality: "1" }
}
create Task TkInsertReservationsCreneau {
className : "io.vertigo.basics.task.TaskEngineProcBatch"
request : "
INSERT INTO reservation_creneau(REC_ID, DATE_LOCALE, MINUTES_DEBUT, MINUTES_FIN, INSTANT_CREATION, AGE_ID)
values (
nextval('SEQ_RESERVATION_CRENEAU'),
#reservationsCreneau.0.dateLocale#,
#reservationsCreneau.0.minutesDebut#,
#reservationsCreneau.0.minutesFin#,
#reservationsCreneau.0.instantCreation#
#reservationsCreneau.0.ageId#);
"
in reservationsCreneau {domain : DoDtReservationCreneau cardinality: "*" }
}
/** Pour la purge des reservation_creneau, creneau, tranche_horaire, plage_horaire */
create Task TkPurgePlageHoraireByDateLocale {
className : "io.vertigo.basics.task.TaskEngineProc"
request : "
delete from creneau c
using tranche_horaire th, plage_horaire ph
where c.trh_id = th.trh_id
and th.plh_id = ph.plh_id
and ph.date_locale < #purgeDate#;
delete from reservation_creneau rc
where rc.date_locale < #purgeDate#;
delete from tranche_horaire th
using plage_horaire ph
where th.plh_id = ph.plh_id
and ph.date_locale < #purgeDate#;
delete from plage_horaire ph
where ph.date_locale < #purgeDate#;
"
in purgeDate {domain : DoPLocalDate cardinality: "1" }
out intSqlRowcount {domain : DoPNombre cardinality: "1" }
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy