Update home.md

Fonctionel/Clause de bannettes/home.md

@@ -10,3 +10,62 @@ Tous les courriers de mes entités et des entités sœurs de mon entité primair
 ```sql
 destination in (select e1.entity_id from entities e1, entities e2 where e1.parent_entity_id = e2.parent_entity_id and e2.entity_id = @my_primary_entity) or destination in (@my_entities)
 ```
+Bannette A emettre (Avis Sequentiel) :
+
+    status in ('COMPLD', 'EAVIS') AND ((res_id, @user) in (SELECT res_id, item_id FROM
+    listinstance WHERE difflist_type = 'AVIS_CIRCUIT' and process_date ISNULL and
+    res_view_letterbox.res_id = res_id order by listinstance_id asc limit 1))
+
+Bannette En attente de réponse (Avis Sequentiel)
+
+    status='EAVIS' and dest_user = @user_id
+
+Bannette Courrier du jour :
+
+    (date_trunc('day', creation_date)= current_date) and (DESTINATION in (@my_entities
+    ,@subentities[@my_entities]))
+
+Bannette Courriers en copie non clos ou sans suite
+
+    (res_id in (select res_id from listinstance WHERE item_type = 'user_id' and item_id = @user_id and item_mode = 'cc') or res_id in (select res_id from listinstance WHERE item_type = 'entity_id' and item_mode = 'cc' and item_id in (@my_entities_id))) and status not in ( 'DEL', 'END', 'SSUITE') and res_id not in (select res_id from res_mark_as_read WHERE user_id = @user_id)
+
+Bannette Courriers en copie clos
+
+    (res_id in (select res_id from listinstance WHERE item_type = 'user_id' and item_id = @user_id and item_mode = 'cc') or res_id in (select res_id from listinstance WHERE item_type = 'entity_id' and item_mode = 'cc' and item_id in (@my_entities_id))) and status in ( 'END') and res_id not in (select res_id from res_mark_as_read WHERE user_id = @user_id)
+
+Bannette Courriers nécessitant un avis
+
+    status = 'EAVIS' AND res_id IN (SELECT res_id FROM listinstance WHERE item_type = 'user_id' AND item_id = @user_id AND item_mode = 'avis' and process_date is NULL
+
+Bannette Courrier de ma direction
+
+    destination in (@my_entities, @subentities[@my_primary_entity]) and (status <> 'DEL' AND status<>'END' AND status <> 'REP' and status <> 'VAL' and status <> 'VALID_DGA' and status <>'VALID_DGS' and status <> 'RET' and status<> 'EAVIS' and status <>'EVIS' and status <> 'EENV' and status <> 'RET_VISA') and dest_user <> @user_id
+
+Bannette Courriers arrivant à expiration (date fin contrat < 3mois)
+
+    custom_fields->>'1' is not null and custom_fields->>'1' <> '' and date(custom_fields->>'1') < now()+ interval '3 months'
+
+Bannette Courrier en retard de traitement
+
+    dest_user = @user_id and (status <> 'DEL' AND status <> 'REP' and status <> 'END') and (now() > process_limit_date)
+
+Bannette Courrier rejeté par le parapheur
+
+    status='REJ_SIGN' and dest_user = @user_id
+
+Bannette Courriers en circulation dans les parapheurs électroniques non présent dans ma bannette courrier à viser
+
+    status in ('ESIG', 'EVIS', 'ATT_MP') and res_id in (Select res_id from listinstance where (item_id = @user_id and process_date is not null and difflist_type = 'VISA_CIRCUIT' AND destination in (@my_entities))OR(item_id != @user_id and difflist_type = 'VISA_CIRCUIT' AND destination in (@my_entities)))
+
+Bannette A viser dont le viseur en attente fait partie de l'une des entités
+
+    res_id IN (SELECT list1.res_id FROM listinstance as list1 LEFT JOIN (SELECT list_lj.* FROM listinstance as list_lj WHERE list_lj.difflist_type = 'VISA_CIRCUIT' AND list_lj.process_date IS NULL AND list_lj.res_id = res_view_letterbox.res_id ORDER BY list_lj.listinstance_id asc LIMIT 1) as list2 ON list1.listinstance_id = list2.listinstance_id WHERE list2.requested_signature = false AND list2.item_id IN (SELECT user_id FROM users_entities WHERE entity_id in(@my_entities)))
+
+Bannette de consultation "Documents déjà visés"
+
+    res_id in (Select res_id from listinstance where item_id = @user_id and process_date is not null and difflist_type = 'VISA_CIRCUIT')
+
+    En ce qui concerne la clause de la bannette :
+
+        Si l'on veut ajouter "non clos" il faut ajouté : < and status != 'END' >
+        Si l'on veut pas des courriers "supprimé" il faut ajouté : and status <> ('DEL')