"rawSql":"SELECT\n creation_date AS \"time\",\n fee\nFROM shippings\nWHERE\n $__timeFilter(creation_date)\nORDER BY 1",
"refId":"A",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"title":"Maarch Courrier",
"transparent":true,
"type":"text"
},
{
"datasource":null,
"fieldConfig":{
"defaults":{
"custom":{},
"mappings":[],
"thresholds":{
"mode":"absolute",
"steps":[
{
"color":"green",
"value":null
},
{
"color":"red",
"value":80
}
]
}
},
"overrides":[]
},
"gridPos":{
"h":5,
"w":4,
"x":0,
"y":3
},
"id":14,
"options":{
"colorMode":"value",
"graphMode":"area",
"justifyMode":"auto",
"orientation":"auto",
"reduceOptions":{
"calcs":[
"count"
],
"fields":"/^Priorité$/",
"values":false
},
"text":{},
"textMode":"auto"
},
"pluginVersion":"7.4.3",
"targets":[
{
"format":"table",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT\n p.label as \"Priorité\",\n\td.description as \"Type de document\",\n\tr.subject as \"Objet\",\n\tentTrait.entity_label as \"Service traitant\",\n\tagTrait.firstname || ' ' || agTrait.lastname as \"Agent traitant\",\n\tTO_CHAR(r.creation_date, 'dd/mm/yyyy') as \"Date de creation\",\n\tTO_CHAR(r.process_limit_date, 'dd/mm/yyyy') as \"Date limite de traitement\",\n\ts.label_status as \"Etat\",\n\tCASE WHEN ar.send_date IS NULL THEN 'AR non envoyé'\n WHEN (ar.send_date - r.creation_date)< INTERVAL '0 DAY' THEN 'AR en retard !'\n ELSE 'AR envoyé'\n END as \"Etat AR\",\n\tDATE_PART('day', ar.send_date - r.creation_date) as \"Delai AR (jour)\",\n TO_CHAR(r.closing_date, 'dd/mm/yyyy') as \"Date de cloture\",\n DATE_PART('day', r.closing_date - r.creation_date) as \"Delai de traitement (jour)\"\nFROM res_letterbox r\nLEFT JOIN doctypes d ON r.type_id = d.type_id\nLEFT JOIN entities entTrait ON entTrait.entity_id = r.destination\nLEFT JOIN users agTrait ON agTrait.id = r.dest_user\nLEFT JOIN status s ON s.id = r.status\nLEFT JOIN priorities p ON p.id = r.priority\nLEFT JOIN acknowledgement_receipts ar ON ar.res_id = r.res_id AND ar.send_date is NOT NULL\nWHERE r.status <> 'DEL'",
"refId":"A",
"select":[
[
{
"params":[
"dest_user"
],
"type":"column"
}
]
],
"table":"res_letterbox",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"timeFrom":null,
"timeShift":null,
"title":"Nombre de Courriers",
"type":"stat"
},
{
"aliasColors":{},
"bars":false,
"dashLength":10,
"dashes":false,
"datasource":null,
"description":"",
"fieldConfig":{
"defaults":{
"color":{},
"custom":{},
"thresholds":{
"mode":"absolute",
"steps":[]
}
},
"overrides":[]
},
"fill":0,
"fillGradient":0,
"gridPos":{
"h":5,
"w":11,
"x":4,
"y":3
},
"hiddenSeries":false,
"id":28,
"legend":{
"avg":false,
"current":false,
"max":false,
"min":false,
"show":true,
"total":false,
"values":false
},
"lines":true,
"linewidth":2,
"nullPointMode":"null",
"options":{
"alertThreshold":true
},
"percentage":false,
"pluginVersion":"7.4.3",
"pointradius":2,
"points":true,
"renderer":"flot",
"seriesOverrides":[],
"spaceLength":10,
"stack":false,
"steppedLine":false,
"targets":[
{
"format":"time_series",
"group":[
{
"params":[
"$__interval",
"none"
],
"type":"time"
}
],
"metricColumn":"none",
"rawQuery":false,
"rawSql":"SELECT\n $__timeGroupAlias(creation_date,$__interval),\n count(res_id) AS \"Arrivés\"\nFROM res_letterbox\nWHERE\n $__timeFilter(creation_date) AND\n category_id <> 'outgoing'\nGROUP BY 1\nORDER BY 1",
"refId":"ARRIVES",
"select":[
[
{
"params":[
"res_id"
],
"type":"column"
},
{
"params":[
"count"
],
"type":"aggregate"
},
{
"params":[
"Arrivés"
],
"type":"alias"
}
]
],
"table":"res_letterbox",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
},
{
"datatype":"varchar",
"name":"",
"params":[
"category_id",
"<>",
"'outgoing'"
],
"type":"expression"
}
]
},
{
"format":"time_series",
"group":[
{
"params":[
"$__interval",
"none"
],
"type":"time"
}
],
"hide":false,
"metricColumn":"none",
"rawQuery":false,
"rawSql":"SELECT\n $__timeGroupAlias(creation_date,$__interval),\n count(res_id) AS \"Départs\"\nFROM res_letterbox\nWHERE\n $__timeFilter(creation_date) AND\n category_id = 'outgoing'\nGROUP BY 1\nORDER BY 1",
"refId":"DEPARTS",
"select":[
[
{
"params":[
"res_id"
],
"type":"column"
},
{
"params":[
"count"
],
"type":"aggregate"
},
{
"params":[
"Départs"
],
"type":"alias"
}
]
],
"table":"res_letterbox",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
},
{
"datatype":"varchar",
"name":"",
"params":[
"category_id",
"=",
"'outgoing'"
],
"type":"expression"
}
]
}
],
"thresholds":[],
"timeFrom":null,
"timeRegions":[],
"timeShift":null,
"title":"Flux entrants /sortants",
"tooltip":{
"shared":true,
"sort":0,
"value_type":"individual"
},
"type":"graph",
"xaxis":{
"buckets":null,
"mode":"time",
"name":null,
"show":true,
"values":[]
},
"yaxes":[
{
"format":"short",
"label":null,
"logBase":1,
"max":null,
"min":null,
"show":true
},
{
"format":"short",
"label":null,
"logBase":1,
"max":null,
"min":null,
"show":true
}
],
"yaxis":{
"align":false,
"alignLevel":null
}
},
{
"datasource":null,
"fieldConfig":{
"defaults":{
"color":{
"mode":"thresholds"
},
"custom":{},
"mappings":[],
"noValue":"Vous êtes à jour !",
"thresholds":{
"mode":"absolute",
"steps":[
{
"color":"light-yellow",
"value":null
},
{
"color":"semi-dark-orange",
"value":20
},
{
"color":"red",
"value":50
}
]
}
},
"overrides":[]
},
"gridPos":{
"h":5,
"w":4,
"x":15,
"y":3
},
"id":16,
"options":{
"orientation":"auto",
"reduceOptions":{
"calcs":[
"count"
],
"fields":"/^Identifiant$/",
"values":false
},
"showThresholdLabels":false,
"showThresholdMarkers":true,
"text":{}
},
"pluginVersion":"7.4.3",
"targets":[
{
"format":"table",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT \n r.res_id as \"Identifiant\",\n\tr.alt_identifier as \"Chrono\",\n\tr.subject as \"Objet\",\n\tagTrait.firstname || ' ' || agTrait.lastname as \"Agent traitant\",\n\tDATE_PART('day', process_limit_date - CURRENT_DATE) as \"Retard (jour)\"\nFROM res_letterbox r \nINNER JOIN users agTrait ON agTrait.id = r.dest_user\nWHERE closing_date is null and (process_limit_date - CURRENT_DATE) < INTERVAL '0 DAY'",
"refId":"A",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"timeFrom":null,
"timeShift":null,
"title":"Courriers en retard",
"type":"gauge"
},
{
"aliasColors":{},
"bars":true,
"dashLength":10,
"dashes":false,
"datasource":null,
"fieldConfig":{
"defaults":{
"custom":{},
"thresholds":{
"mode":"absolute",
"steps":[]
}
},
"overrides":[]
},
"fill":1,
"fillGradient":0,
"gridPos":{
"h":6,
"w":19,
"x":0,
"y":8
},
"hiddenSeries":false,
"id":12,
"legend":{
"avg":false,
"current":false,
"max":false,
"min":false,
"show":false,
"total":false,
"values":false
},
"lines":false,
"linewidth":1,
"nullPointMode":"null",
"options":{
"alertThreshold":true
},
"percentage":false,
"pluginVersion":"7.4.3",
"pointradius":2,
"points":false,
"renderer":"flot",
"seriesOverrides":[],
"spaceLength":10,
"stack":false,
"steppedLine":false,
"targets":[
{
"format":"time_series",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"select count(1), destination, creation_date as \"time\" from res_letterbox where destination is not null AND $__timeFilter(creation_date) group by destination, creation_date",
"refId":"A",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"thresholds":[],
"timeFrom":null,
"timeRegions":[],
"timeShift":null,
"title":"Courriers par service",
"tooltip":{
"shared":false,
"sort":0,
"value_type":"individual"
},
"type":"graph",
"xaxis":{
"buckets":null,
"mode":"series",
"name":null,
"show":true,
"values":[
"count"
]
},
"yaxes":[
{
"$$hashKey":"object:950",
"format":"none",
"label":null,
"logBase":1,
"max":null,
"min":null,
"show":true
},
{
"$$hashKey":"object:951",
"format":"none",
"label":null,
"logBase":1,
"max":null,
"min":null,
"show":true
}
],
"yaxis":{
"align":false,
"alignLevel":null
}
},
{
"datasource":null,
"fieldConfig":{
"defaults":{
"custom":{},
"mappings":[],
"thresholds":{
"mode":"absolute",
"steps":[
{
"color":"green",
"value":null
},
{
"color":"red",
"value":80
}
]
}
},
"overrides":[]
},
"gridPos":{
"h":3,
"w":4,
"x":0,
"y":14
},
"id":20,
"options":{
"colorMode":"value",
"graphMode":"area",
"justifyMode":"auto",
"orientation":"auto",
"reduceOptions":{
"calcs":[
"count"
],
"fields":"/^Chrono$/",
"values":false
},
"text":{},
"textMode":"auto"
},
"pluginVersion":"7.4.3",
"targets":[
{
"format":"table",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT \n\tr.alt_identifier as \"Chrono\",\n\tr.subject as \"Objet\",\n\tagTrait.firstname || ' ' || agTrait.lastname as \"Agent traitant\"\nFROM res_letterbox r\nINNER JOIN users agTrait ON agTrait.id = r.dest_user\nWHERE r.res_id NOT IN (\n\tSELECT res_id FROM acknowledgement_receipts WHERE send_date is NOT NULL\n)",
"refId":"A",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"timeFrom":null,
"timeShift":null,
"title":"AR à envoyer",
"type":"stat"
},
{
"aliasColors":{},
"bars":false,
"dashLength":10,
"dashes":false,
"datasource":null,
"fieldConfig":{
"defaults":{
"custom":{}
},
"overrides":[]
},
"fill":0,
"fillGradient":0,
"gridPos":{
"h":9,
"w":7,
"x":4,
"y":14
},
"hiddenSeries":false,
"id":24,
"legend":{
"avg":false,
"current":false,
"max":false,
"min":false,
"show":true,
"total":false,
"values":false
},
"lines":true,
"linewidth":1,
"nullPointMode":"null as zero",
"options":{
"alertThreshold":false
},
"percentage":false,
"pluginVersion":"7.4.3",
"pointradius":2,
"points":true,
"renderer":"flot",
"seriesOverrides":[],
"spaceLength":10,
"stack":false,
"steppedLine":false,
"targets":[
{
"format":"time_series",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT \n count(ar.res_id) as \"AR généré\",\n date_trunc('month', r.creation_date) as \"time\"\nFROM res_letterbox r\nLEFT JOIN acknowledgement_receipts ar ON r.res_id = ar.res_id AND ar.creation_date is not null\nGROUP BY time",
"refId":"A",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
},
{
"format":"time_series",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT \n count(ar.res_id) as \"AR envoyé à temps\",\n date_trunc('month', r.creation_date) as \"time\"\nFROM res_letterbox r\nINNER JOIN acknowledgement_receipts ar ON r.res_id = ar.res_id AND ar.send_date is not null and (process_limit_date - ar.send_date) > INTERVAL '0 DAY'\nGROUP BY time",
"refId":"B",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"thresholds":[],
"timeFrom":null,
"timeRegions":[],
"timeShift":null,
"title":"AR (en fonction de la date de création du courrier)",
"tooltip":{
"shared":true,
"sort":0,
"value_type":"individual"
},
"type":"graph",
"xaxis":{
"buckets":null,
"mode":"time",
"name":null,
"show":true,
"values":[]
},
"yaxes":[
{
"$$hashKey":"object:617",
"decimals":0,
"format":"locale",
"label":"",
"logBase":1,
"max":null,
"min":null,
"show":true
},
{
"$$hashKey":"object:618",
"format":"short",
"label":null,
"logBase":1,
"max":null,
"min":null,
"show":true
}
],
"yaxis":{
"align":false,
"alignLevel":null
}
},
{
"aliasColors":{},
"bars":false,
"dashLength":10,
"dashes":false,
"datasource":null,
"fieldConfig":{
"defaults":{
"custom":{}
},
"overrides":[]
},
"fill":1,
"fillGradient":0,
"gridPos":{
"h":9,
"w":8,
"x":11,
"y":14
},
"hiddenSeries":false,
"id":26,
"legend":{
"avg":false,
"current":false,
"max":false,
"min":false,
"show":true,
"total":false,
"values":false
},
"lines":true,
"linewidth":1,
"nullPointMode":"null",
"options":{
"alertThreshold":true
},
"percentage":false,
"pluginVersion":"7.4.3",
"pointradius":2,
"points":false,
"renderer":"flot",
"seriesOverrides":[],
"spaceLength":10,
"stack":false,
"steppedLine":false,
"targets":[
{
"format":"time_series",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT \n AVG(DATE_PART('day', ar.send_date - r.creation_date)) as \"Delai AR (jour)\",\n date_trunc('month', r.creation_date) as \"time\"\nFROM res_letterbox r\nINNER JOIN acknowledgement_receipts ar ON r.res_id = ar.res_id AND ar.send_date is not null\nGROUP BY time",
"refId":"A",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"thresholds":[],
"timeFrom":null,
"timeRegions":[],
"timeShift":null,
"title":"Délai moyen AR (en fonction de la date de creation du courrier)",
"tooltip":{
"shared":true,
"sort":0,
"value_type":"individual"
},
"type":"graph",
"xaxis":{
"buckets":null,
"mode":"time",
"name":null,
"show":true,
"values":[]
},
"yaxes":[
{
"$$hashKey":"object:1395",
"format":"short",
"label":null,
"logBase":1,
"max":null,
"min":null,
"show":true
},
{
"$$hashKey":"object:1396",
"format":"short",
"label":null,
"logBase":1,
"max":null,
"min":null,
"show":true
}
],
"yaxis":{
"align":false,
"alignLevel":null
}
},
{
"datasource":null,
"fieldConfig":{
"defaults":{
"custom":{},
"mappings":[],
"thresholds":{
"mode":"absolute",
"steps":[
{
"color":"green",
"value":null
},
{
"color":"red",
"value":80
}
]
}
},
"overrides":[]
},
"gridPos":{
"h":3,
"w":4,
"x":0,
"y":17
},
"id":18,
"options":{
"colorMode":"value",
"graphMode":"area",
"justifyMode":"auto",
"orientation":"auto",
"reduceOptions":{
"calcs":[
"mean"
],
"fields":"",
"values":false
},
"text":{},
"textMode":"auto"
},
"pluginVersion":"7.4.3",
"targets":[
{
"format":"table",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT DATE_PART('day', ar.send_date - r.creation_date) as \"Delai (jour)\" FROM res_letterbox r\nINNER JOIN acknowledgement_receipts ar ON r.res_id = ar.res_id\nWHERE ar.send_date is not null",
"refId":"A",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"timeFrom":null,
"timeShift":null,
"title":"Delai Moyen AR (en jour)",
"type":"stat"
},
{
"datasource":null,
"description":"",
"fieldConfig":{
"defaults":{
"custom":{},
"mappings":[],
"max":100,
"thresholds":{
"mode":"absolute",
"steps":[
{
"color":"red",
"value":null
},
{
"color":"green",
"value":50
}
]
},
"unit":"percent"
},
"overrides":[]
},
"gridPos":{
"h":3,
"w":4,
"x":0,
"y":20
},
"id":22,
"options":{
"orientation":"auto",
"reduceOptions":{
"calcs":[
"mean"
],
"fields":"",
"values":true
},
"showThresholdLabels":false,
"showThresholdMarkers":false,
"text":{}
},
"pluginVersion":"7.4.3",
"targets":[
{
"format":"table",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT (count(1) * 100) / (SELECT count(1)\nFROM res_letterbox r\nINNER JOIN acknowledgement_receipts ar ON ar.res_id = r.res_id\nWHERE ar.send_date is not null AND $__timeFilter(r.creation_date)\n) as \"total\"\nFROM res_letterbox r\nINNER JOIN acknowledgement_receipts ar ON ar.res_id = r.res_id\nWHERE ar.send_date is not null and (process_limit_date - ar.send_date) > INTERVAL '0 DAY' AND $__timeFilter(r.creation_date)",
"refId":"A",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"timeFrom":null,
"timeShift":null,
"title":"% AR envoyé à temps",
"type":"gauge"
},
{
"aliasColors":{},
"bars":false,
"dashLength":10,
"dashes":false,
"datasource":null,
"fieldConfig":{
"defaults":{
"custom":{}
},
"overrides":[]
},
"fill":1,
"fillGradient":0,
"gridPos":{
"h":6,
"w":19,
"x":0,
"y":23
},
"hiddenSeries":false,
"id":8,
"legend":{
"avg":false,
"current":false,
"max":false,
"min":false,
"show":true,
"total":false,
"values":false
},
"lines":true,
"linewidth":1,
"nullPointMode":"null",
"options":{
"alertThreshold":true
},
"percentage":false,
"pluginVersion":"7.4.3",
"pointradius":2,
"points":false,
"renderer":"flot",
"seriesOverrides":[],
"spaceLength":10,
"stack":false,
"steppedLine":false,
"targets":[
{
"format":"time_series",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT\n AVG(DATE_PART('day', closing_date - creation_date)) as \"Délai moyen de clôture (jour)\",\n date_trunc('month', closing_date) as \"time\"\nFROM res_letterbox\nWHERE closing_date is not null group by time",
"refId":"A",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"thresholds":[],
"timeFrom":null,
"timeRegions":[],
"timeShift":null,
"title":"Délai moyen de clôture (en fonction de la date de clôture)",
"tooltip":{
"shared":true,
"sort":0,
"value_type":"individual"
},
"type":"graph",
"xaxis":{
"buckets":null,
"mode":"time",
"name":null,
"show":true,
"values":[]
},
"yaxes":[
{
"$$hashKey":"object:1168",
"format":"short",
"label":null,
"logBase":1,
"max":null,
"min":"0",
"show":true
},
{
"$$hashKey":"object:1169",
"format":"short",
"label":null,
"logBase":1,
"max":null,
"min":null,
"show":true
}
],
"yaxis":{
"align":false,
"alignLevel":null
}
},
{
"aliasColors":{},
"bars":false,
"dashLength":10,
"dashes":false,
"datasource":null,
"description":"",
"fieldConfig":{
"defaults":{
"custom":{},
"thresholds":{
"mode":"absolute",
"steps":[]
}
},
"overrides":[]
},
"fill":0,
"fillGradient":0,
"gridPos":{
"h":6,
"w":19,
"x":0,
"y":29
},
"hiddenSeries":false,
"id":6,
"legend":{
"avg":false,
"current":false,
"max":false,
"min":false,
"show":true,
"total":false,
"values":false
},
"lines":true,
"linewidth":1,
"nullPointMode":"null",
"options":{
"alertThreshold":false
},
"percentage":false,
"pluginVersion":"7.4.3",
"pointradius":2,
"points":false,
"renderer":"flot",
"seriesOverrides":[],
"spaceLength":10,
"stack":false,
"steppedLine":false,
"targets":[
{
"format":"table",
"group":[
{
"params":[
"$__interval",
"none"
],
"type":"time"
}
],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT \n count(res_id) as \"Courriers traités\",\n date_trunc('month', creation_date) as \"time\"\nFROM res_letterbox r\nWHERE closing_date is NOT NULL\nGROUP BY time",
"refId":"A",
"select":[
[
{
"params":[
"res_id"
],
"type":"column"
},
{
"params":[
"count"
],
"type":"aggregate"
},
{
"params":[
"res_id"
],
"type":"alias"
}
]
],
"table":"res_letterbox",
"timeColumn":"closing_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
},
{
"format":"time_series",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT \n count(res_id) as \"Courriers traités dans les délais\",\n date_trunc('month', creation_date) as \"time\"\nFROM res_letterbox r\nWHERE closing_date is NOT NULL AND (process_limit_date - closing_date) > INTERVAL '0 DAY'\nGROUP BY time",
"refId":"B",
"select":[
[
{
"params":[
"fee"
],
"type":"column"
}
]
],
"table":"shippings",
"timeColumn":"creation_date",
"timeColumnType":"timestamp",
"where":[
{
"name":"$__timeFilter",
"params":[],
"type":"macro"
}
]
}
],
"thresholds":[],
"timeFrom":null,
"timeRegions":[],
"timeShift":null,
"title":"Courriers traités (en fonction de la date de création)",
"tooltip":{
"shared":true,
"sort":0,
"value_type":"individual"
},
"transformations":[],
"type":"graph",
"xaxis":{
"buckets":null,
"mode":"time",
"name":null,
"show":true,
"values":[]
},
"yaxes":[
{
"$$hashKey":"object:1298",
"decimals":0,
"format":"locale",
"label":"",
"logBase":1,
"max":null,
"min":null,
"show":true
},
{
"$$hashKey":"object:1299",
"format":"short",
"label":null,
"logBase":1,
"max":null,
"min":null,
"show":true
}
],
"yaxis":{
"align":false,
"alignLevel":null
}
},
{
"datasource":null,
"fieldConfig":{
"defaults":{
"custom":{
"align":null,
"filterable":false
},
"mappings":[
{
"from":"",
"id":1,
"text":"",
"to":"",
"type":1,
"value":""
}
],
"thresholds":{
"mode":"absolute",
"steps":[
{
"color":"green",
"value":null
},
{
"color":"red",
"value":80
}
]
}
},
"overrides":[]
},
"gridPos":{
"h":18,
"w":19,
"x":0,
"y":35
},
"id":4,
"options":{
"showHeader":true,
"sortBy":[
{
"desc":true,
"displayName":"Delai AR (jour)"
}
]
},
"pluginVersion":"7.4.3",
"repeat":null,
"targets":[
{
"format":"table",
"group":[],
"metricColumn":"none",
"rawQuery":true,
"rawSql":"SELECT\n p.label as \"Priorité\",\n\td.description as \"Type de document\",\n\tr.subject as \"Objet\",\n\tentTrait.entity_label as \"Service traitant\",\n\tagTrait.firstname || ' ' || agTrait.lastname as \"Agent traitant\",\n\tTO_CHAR(r.creation_date, 'dd/mm/yyyy') as \"Date de creation\",\n\tTO_CHAR(r.process_limit_date, 'dd/mm/yyyy') as \"Date limite de traitement\",\n\ts.label_status as \"Etat\",\n\tCASE WHEN ar.send_date IS NULL THEN 'AR non envoyé'\n WHEN (ar.send_date - r.creation_date)< INTERVAL '0 DAY' THEN 'AR en retard !'\n ELSE 'AR envoyé'\n END as \"Etat AR\",\n\tDATE_PART('day', ar.send_date - r.creation_date) as \"Delai AR (jour)\",\n TO_CHAR(r.closing_date, 'dd/mm/yyyy') as \"Date de cloture\",\n DATE_PART('day', r.closing_date - r.creation_date) as \"Delai de traitement (jour)\"\nFROM res_letterbox r\nLEFT JOIN doctypes d ON r.type_id = d.type_id\nLEFT JOIN entities entTrait ON entTrait.entity_id = r.destination\nLEFT JOIN users agTrait ON agTrait.id = r.dest_user\nLEFT JOIN status s ON s.id = r.status\nLEFT JOIN priorities p ON p.id = r.priority\nLEFT JOIN acknowledgement_receipts ar ON ar.res_id = r.res_id AND ar.send_date is NOT NULL\nWHERE r.status <> 'DEL' AND $__timeFilter(r.creation_date);",
