[FogBugz #235] Afficher nombre de tickets bloqués #297

New Issue

fbenoist68 · 2023-07-04T00:02:06+02:00

fbenoist68 commented

2023-07-04 00:02:06 +02:00

Assigned To: Frederik Benoist
Project: Mobile Retail
Area: Divers
Category: Fonctionnalité
Priority: Ce serait bien !

Assigned To: Frederik Benoist Project: Mobile Retail Area: Divers Category: Fonctionnalité Priority: Ce serait bien !

fbenoist68 commented

2023-07-04 00:02:10 +02:00

Poster

Event ID: 1035
Date: 2013-11-30T12:35:56Z
Person: Frederik Benoist
Description: Modifié par Frederik Benoist

WITH max_insertion AS (SELECT id_structure, id_caisse, MAX (fdate_insertion) AS max_date

FROM log_boutique

WHERE TRUNC (fdate_insertion) > TRUNC (SYSDATE) - 15

GROUP BY id_structure, id_caisse)

SELECT d.libelle AS distributeur,

TRIM (s.nom) AS boutique,

trim(pp.nom) as pays,

TRUNC (TO_NUMBER (lb.MESSAGE) / 4) AS nombre_bloque,

s.tel1,

lb.id_structure,

lb.id_caisse,

lb.fdate_insertion AS date_dernier_controle,

lb.ip,

sc.VERSION_INSTALLEE,

cbs.valeur AS status,

SUM (TRUNC (TO_NUMBER (lb.MESSAGE) / 4)) OVER (PARTITION BY s.id_distrib) AS total_distrib,

SUM (TRUNC (TO_NUMBER (lb.MESSAGE) / 4)) OVER (PARTITION BY 1) AS total_tous_distrib, max(lb.VERSION) as version_alerteticketbloque

FROM (select * from log_boutique where TRUNC (fdate_insertion) > TRUNC (SYSDATE - 2))lb

JOIN structure s

ON s.id_structure = lb.id_structure

join param_pays pp on pp.id_pays = s.id_pays

JOIN distributeur d

ON d.id_distrib = s.id_distrib

JOIN max_insertion mi

ON mi.id_structure = lb.id_structure AND mi.id_caisse = lb.id_caisse AND mi.max_date = lb.fdate_insertion

LEFT JOIN critereboutique_structure cbs

ON cbs.id_structure = s.id_structure AND cbs.id_critere = 4

left join structure_caisse sc on lb.id_structure = sc.id_structure and lb.id_caisse = sc.id_caisse

WHERE TRUNC (TO_NUMBER (lb.MESSAGE) / 4) > 1

AND REGEXP_LIKE (lb.MESSAGE, ':digit:')

--and trim(nvl(lb.message,' ')) <> ''

AND lb.nom_module = 'AlerteTicketBloque'

AND s.magasin_demo = 0

AND s.id_niveau = 4

AND s.statut = 2

and s.id_pays like case when :pid_pays = '' or :pid_pays is null then '%' else :pid_pays end

GROUP BY d.libelle,

TRIM (s.nom),

trim(pp.nom),

lb.MESSAGE,

s.tel1,

lb.id_structure,

lb.id_caisse,

sc.VERSION_INSTALLEE,

lb.fdate_insertion,

lb.ip,

cbs.valeur,

s.id_distrib

ORDER BY 1, 2

Event ID: 1035 Date: 2013-11-30T12:35:56Z Person: Frederik Benoist Description: Modifié par Frederik Benoist <p>  </p> <div> WITH max_insertion AS (SELECT id_structure, id_caisse, MAX (fdate_insertion) AS max_date</div> <div>                          FROM log_boutique</div> <div>                         WHERE TRUNC (fdate_insertion) > TRUNC (SYSDATE) - 15</div> <div>                        GROUP BY id_structure, id_caisse)</div> <div> SELECT d.libelle AS distributeur,</div> <div>        TRIM (s.nom) AS boutique,</div> <div>        trim(pp.nom) as pays,</div> <div>        TRUNC (TO_NUMBER (lb.MESSAGE) / 4) AS nombre_bloque,</div> <div>        s.tel1,</div> <div>        lb.id_structure,</div> <div>        lb.id_caisse,</div> <div>        lb.fdate_insertion AS date_dernier_controle,</div> <div>        lb.ip,</div> <div>        sc.VERSION_INSTALLEE,</div> <div>        cbs.valeur AS status,</div> <div>        SUM (TRUNC (TO_NUMBER (lb.MESSAGE) / 4)) OVER (PARTITION BY s.id_distrib) AS total_distrib,</div> <div>        SUM (TRUNC (TO_NUMBER (lb.MESSAGE) / 4)) OVER (PARTITION BY 1) AS total_tous_distrib, max(lb.VERSION) as version_alerteticketbloque</div> <div>   FROM (select * from log_boutique where TRUNC (fdate_insertion) > TRUNC (SYSDATE - 2))lb</div> <div>        JOIN structure s</div> <div>            ON s.id_structure = lb.id_structure</div> <div> join param_pays pp on pp.id_pays = s.id_pays</div> <div>        JOIN distributeur d</div> <div>            ON d.id_distrib = s.id_distrib</div> <div>        JOIN max_insertion mi</div> <div>            ON mi.id_structure = lb.id_structure AND mi.id_caisse = lb.id_caisse AND mi.max_date = lb.fdate_insertion</div> <div>        LEFT JOIN critereboutique_structure cbs</div> <div>            ON cbs.id_structure = s.id_structure AND cbs.id_critere = 4</div> <div>            left join structure_caisse sc on lb.id_structure = sc.id_structure and lb.id_caisse = sc.id_caisse</div> <div>  WHERE  TRUNC (TO_NUMBER (lb.MESSAGE) / 4) > 1</div> <div>        AND REGEXP_LIKE (lb.MESSAGE, '[[:digit:]]')</div> <div>        --and trim(nvl(lb.message,' ')) <> ''</div> <div>        AND lb.nom_module = 'AlerteTicketBloque'</div> <div>        AND s.magasin_demo = 0</div> <div>        AND s.id_niveau = 4</div> <div>        AND s.statut = 2 </div> <div> and s.id_pays like case when :pid_pays = '' or :pid_pays is null then '%' else :pid_pays end</div> <div> GROUP BY d.libelle,</div> <div>          TRIM (s.nom),</div> <div> trim(pp.nom),</div> <div>          lb.MESSAGE,</div> <div>          s.tel1,</div> <div>          lb.id_structure,</div> <div>          lb.id_caisse,</div> <div>            sc.VERSION_INSTALLEE,</div> <div>          lb.fdate_insertion,</div> <div>          lb.ip,</div> <div>          cbs.valeur,</div> <div>          s.id_distrib</div> <div> ORDER BY 1, 2</div>

fbenoist68 commented

2023-07-04 00:02:13 +02:00

Poster

Event ID: 1034
Date: 2013-11-30T12:35:45Z
Person: Frederik Benoist
Description: Attribué à Frederik Benoist par Frederik Benoist

Event ID: 1034 Date: 2013-11-30T12:35:45Z Person: Frederik Benoist Description: Attribué à Frederik Benoist par Frederik Benoist

fbenoist68 commented

2023-07-04 00:02:15 +02:00

Poster

Event ID: 1033
Date: 2013-11-30T12:35:45Z
Person: Frederik Benoist
Description: Ouvert par Frederik Benoist

Event ID: 1033 Date: 2013-11-30T12:35:45Z Person: Frederik Benoist Description: Ouvert par Frederik Benoist

fbenoist68 referenced this issue

2023-07-04 07:19:00 +02:00