Aller à : navigation, rechercher

Maarch Courrier/latest/fr/FAQ/Optimiser vos bannettes

Il arrive un moment où votre base de données commence à prendre du volume et inévitablement, vous commencerez à avoir quelques lenteurs sur votre page d'accueil.

Cela s'explique sur le fait que l'affichage du nombre de courriers se base sur la vue res_view_letterbox qui pointe sur l'ensemble des courriers enregistrés (avec 100k lignes les requêtes commencent à fatiguer ...).


La solution que nous vous proposons est de créer une vue supplémentaire destinée à l'affichage des courriers dans les bannettes.

Pour cela, plusieurs actions seront à réaliser :

  • créer une vue supplémentaire se basant sur res_view_letterbox
  • modifier le php de modules/baskets/ajaxNbResInBasket.php
  • modifier le php de apps/maarch_entreprise/documents_list_with_attachments.php


Procédure

Création de la vue res_view_letterbox_baskets

Basez-vous sur la vue res_view_letterbox et rajouter dans la clause WHERE une limitation sur la date de création des courrier :

  • Par exemple, si les courriers qui sont actifs dans les bannettes ne remontent pas plus des 6 derniers mois, vous pourrez rajouter : creation_date > (CURRENT_TIMESTAMP - INTERVAL '183')
  • Voici la requête de la vue pour la version 1.5.1 :
 CREATE OR REPLACE VIEW res_view_letterbox_baskets AS 
  SELECT r.tablename,
    r.is_multi_docservers,
    r.res_id,
    r.type_id,
    r.policy_id,
    r.cycle_id,
    d.description AS type_label,
    d.doctypes_first_level_id,
    dfl.doctypes_first_level_label,
    dfl.css_style AS doctype_first_level_style,
    d.doctypes_second_level_id,
    dsl.doctypes_second_level_label,
    dsl.css_style AS doctype_second_level_style,
    r.format,
    r.typist,
    r.creation_date,
    r.relation,
    r.docserver_id,
    r.folders_system_id,
    f.folder_id,
    f.destination AS folder_destination,
    f.is_frozen AS folder_is_frozen,
    r.path,
    r.filename,
    r.fingerprint,
    r.offset_doc,
    r.filesize,
    r.status,
    r.work_batch,
    r.arbatch_id,
    r.arbox_id,
    r.page_count,
    r.is_paper,
    r.doc_date,
    r.scan_date,
    r.scan_user,
    r.scan_location,
    r.scan_wkstation,
    r.scan_batch,
    r.doc_language,
    r.description,
    r.source,
    r.author,
    r.reference_number,
    r.custom_t1 AS doc_custom_t1,
    r.custom_t2 AS doc_custom_t2,
    r.custom_t3 AS doc_custom_t3,
    r.custom_t4 AS doc_custom_t4,
    r.custom_t5 AS doc_custom_t5,
    r.custom_t6 AS doc_custom_t6,
    r.custom_t7 AS doc_custom_t7,
    r.custom_t8 AS doc_custom_t8,
    r.custom_t9 AS doc_custom_t9,
    r.custom_t10 AS doc_custom_t10,
    r.custom_t11 AS doc_custom_t11,
    r.custom_t12 AS doc_custom_t12,
    r.custom_t13 AS doc_custom_t13,
    r.custom_t14 AS doc_custom_t14,
    r.custom_t15 AS doc_custom_t15,
    r.custom_d1 AS doc_custom_d1,
    r.custom_d2 AS doc_custom_d2,
    r.custom_d3 AS doc_custom_d3,
    r.custom_d4 AS doc_custom_d4,
    r.custom_d5 AS doc_custom_d5,
    r.custom_d6 AS doc_custom_d6,
    r.custom_d7 AS doc_custom_d7,
    r.custom_d8 AS doc_custom_d8,
    r.custom_d9 AS doc_custom_d9,
    r.custom_d10 AS doc_custom_d10,
    r.custom_n1 AS doc_custom_n1,
    r.custom_n2 AS doc_custom_n2,
    r.custom_n3 AS doc_custom_n3,
    r.custom_n4 AS doc_custom_n4,
    r.custom_n5 AS doc_custom_n5,
    r.custom_f1 AS doc_custom_f1,
    r.custom_f2 AS doc_custom_f2,
    r.custom_f3 AS doc_custom_f3,
    r.custom_f4 AS doc_custom_f4,
    r.custom_f5 AS doc_custom_f5,
    f.foldertype_id,
    ft.foldertype_label,
    f.custom_t1 AS fold_custom_t1,
    f.custom_t2 AS fold_custom_t2,
    f.custom_t3 AS fold_custom_t3,
    f.custom_t4 AS fold_custom_t4,
    f.custom_t5 AS fold_custom_t5,
    f.custom_t6 AS fold_custom_t6,
    f.custom_t7 AS fold_custom_t7,
    f.custom_t8 AS fold_custom_t8,
    f.custom_t9 AS fold_custom_t9,
    f.custom_t10 AS fold_custom_t10,
    f.custom_t11 AS fold_custom_t11,
    f.custom_t12 AS fold_custom_t12,
    f.custom_t13 AS fold_custom_t13,
    f.custom_t14 AS fold_custom_t14,
    f.custom_t15 AS fold_custom_t15,
    f.custom_d1 AS fold_custom_d1,
    f.custom_d2 AS fold_custom_d2,
    f.custom_d3 AS fold_custom_d3,
    f.custom_d4 AS fold_custom_d4,
    f.custom_d5 AS fold_custom_d5,
    f.custom_d6 AS fold_custom_d6,
    f.custom_d7 AS fold_custom_d7,
    f.custom_d8 AS fold_custom_d8,
    f.custom_d9 AS fold_custom_d9,
    f.custom_d10 AS fold_custom_d10,
    f.custom_n1 AS fold_custom_n1,
    f.custom_n2 AS fold_custom_n2,
    f.custom_n3 AS fold_custom_n3,
    f.custom_n4 AS fold_custom_n4,
    f.custom_n5 AS fold_custom_n5,
    f.custom_f1 AS fold_custom_f1,
    f.custom_f2 AS fold_custom_f2,
    f.custom_f3 AS fold_custom_f3,
    f.custom_f4 AS fold_custom_f4,
    f.custom_f5 AS fold_custom_f5,
    f.is_complete AS fold_complete,
    f.status AS fold_status,
    f.subject AS fold_subject,
    f.parent_id AS fold_parent_id,
    f.folder_level,
    f.folder_name,
    f.creation_date AS fold_creation_date,
    r.initiator,
    r.destination,
    r.dest_user,
    r.confidentiality,
    mlb.category_id,
    mlb.exp_contact_id,
    mlb.exp_user_id,
    mlb.dest_user_id,
    mlb.dest_contact_id,
    mlb.address_id,
    mlb.nature_id,
    mlb.alt_identifier,
    mlb.admission_date,
    mlb.answer_type_bitmask,
    mlb.other_answer_desc,
    mlb.process_limit_date,
    mlb.closing_date,
    mlb.alarm1_date,
    mlb.alarm2_date,
    mlb.flag_notif,
    mlb.flag_alarm1,
    mlb.flag_alarm2,
    mlb.is_multicontacts,
    r.video_user,
    r.video_time,
    r.video_batch,
    r.subject,
    r.identifier,
    r.title,
    r.priority,
    mlb.process_notes,
    r.locker_user_id,
    r.locker_time,
    ca.case_id,
    ca.case_label,
    ca.case_description,
    en.entity_label,
    en.entity_type AS entitytype,
    cont.contact_id,
    cont.firstname AS contact_firstname,
    cont.lastname AS contact_lastname,
    cont.society AS contact_society,
    u.lastname AS user_lastname,
    u.firstname AS user_firstname,
    list.item_id AS dest_user_from_listinstance,
    list.viewed,
    r.is_frozen AS res_is_frozen,
    COALESCE(att.count_attachment, 0::bigint) AS count_attachment
   FROM doctypes d,
    doctypes_first_level dfl,
    doctypes_second_level dsl,
    ar_batch a
     RIGHT JOIN res_letterbox r ON r.arbatch_id = a.arbatch_id
     LEFT JOIN ( SELECT res_attachments.res_id_master,
            count(res_attachments.res_id_master) AS count_attachment
           FROM res_attachments
          WHERE res_attachments.status::text <> 'DEL'::text
          GROUP BY res_attachments.res_id_master) att ON r.res_id = att.res_id_master
     LEFT JOIN entities en ON r.destination::text = en.entity_id::text
     LEFT JOIN folders f ON r.folders_system_id = f.folders_system_id
     LEFT JOIN cases_res cr ON r.res_id = cr.res_id
     LEFT JOIN mlb_coll_ext mlb ON mlb.res_id = r.res_id
     LEFT JOIN foldertypes ft ON f.foldertype_id = ft.foldertype_id AND f.status::text <> 'DEL'::text
     LEFT JOIN cases ca ON cr.case_id = ca.case_id
     LEFT JOIN contacts_v2 cont ON mlb.exp_contact_id = cont.contact_id OR mlb.dest_contact_id = cont.contact_id
     LEFT JOIN users u ON mlb.exp_user_id::text = u.user_id::text OR mlb.dest_user_id::text = u.user_id::text
     LEFT JOIN listinstance list ON r.res_id = list.res_id AND list.item_mode::text = 'dest'::text
  WHERE r.type_id = d.type_id AND d.doctypes_first_level_id = dfl.doctypes_first_level_id AND d.doctypes_second_level_id = dsl.doctypes_second_level_id 
     and r.creation_date > (CURRENT_TIMESTAMP - INTERVAL '183');

Modification de ajaxNbResInBasket

Une fois la vue créée, il faut changer la vue cible dans les requêtes qui vont compter le nombre de courriers depuis la page d'accueil :

* Environ ligne 14, modifier la vue par '''res_view_letterbox_baskets''' : 
 $db->query('select * from res_view_letterbox_baskets'
 . ' where ' . $_SESSION['user']['baskets'][$i]['clause'], true);

Modification de documents_list_with_attachments

Il faut changer la vue cible dans les requêtes qui vont lister les courriers depuis les bannettes :

  • Environ ligne 81, on va changer la cible de la vue pour la partie bannette :
 
//Basket information
 if(!empty($_SESSION['current_basket']['view'])) {
     $table='res_view_letterbox_baskets';
 } else {
     $table = $_SESSION['current_basket']['table'];
 }

Voila! Les banettes des utilisateurs devraient être plus réactives !


Évaluez bien votre périmètre maximal de l’ensemble de vos bannettes ou vous pourrez perdre des courriers dans celles-ci.
  • Par exemple, si une bannette peut potentiellement avoir des courriers qui date de plus de 6 mois, ceux-ci n’apparaitront plus dans cette bannette.