Faire une vue liste à filtres multicritères dynamiques et sur mesure (SQL)

K2 propose un premier niveau de filtre dans les list views. Cette fonction permet d’effectuer des recherches rapides ou de créer des filtres statiques. Nous allons voir, dans cet article, comment faire pour disposer d’un mécanisme de filtres dynamiques.

filtres
En vert, les filtres dynamiques et sur mesure. En orange, les filtres statiques et standards

Celles et ceux d’entre vous qui ont cherché à mettre en place cette fonctionnalité se sont frottés à une contrainte de conception (liée à la méthode Get List des smartobjects). En effet, chaque combinaison de filtre appelle la méthode Get List du smartobject avec des paramètres différents. Soient 2 appels pour 2 champs, 6 pour 3 champs, 24 pour 4 champs… Très vite, on réalise la complexité de cette problématique.

La conception SQL

La solution que nous allons voir ensemble repose sur les procédures stockées SQL, donc pour cette source de données uniquement :

Dans notre exemple, nous allons prendre une table basique (4 colonnes dont l’identifiant) :

USE [BaseTest]
GO

/****** Object:  Table [dbo].[TableTest]    Script Date: 9/11/2017 5:04:05 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TableTest](
	[ColonneIdentifiant] [bigint] IDENTITY(1,1) NOT NULL,
	[ColonneTexte] [nvarchar](100) NULL,
	[ColonneDate] [datetime] NULL,
	[ColonneNombre] [bigint] NULL,
 CONSTRAINT [PK_TableTest] PRIMARY KEY CLUSTERED 
(
	[ColonneIdentifiant] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Voici la procédure stockée à utiliser pour notre exemple. J’ai inséré des commentaires pour simplifier les modifications

USE [BaseTest]
GO

/****** Object:  StoredProcedure [dbo].[TableTestFiltre]    Script Date: 9/11/2017 5:11:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[TableTestFiltre] 
-- Les paramètres SQL servent de filtres
  @FiltreId bigint = null, -- la colonne ID
  @FiltreTexte nvarchar(100) =null, -- la colonne texte
  @FiltreNombre bigint = null, -- la colonne nombre
  @FilterDate datetime= null, -- la colone datetime
  @pMaxReturned nvarchar(100) = null -- la colonne pour le nombre de ligne (bonus)
AS
BEGIN
      SET NOCOUNT ON;

DECLARE  @FiltreTableReturn TABLE(
-- Cette table temporaire va récupérer le resultat de la requête
       [ColonneIdentifiant] [bigint]  NOT NULL,
       [ColonneTexte] [nvarchar](100) NULL,
       [ColonneNombre] [bigint] NULL,
       [ColonneDate] [datetime] Null
) 

-- La requète se construit en fonction des paramètres qui ont été renseignés
DECLARE @query nvarchar(max) = 'SELECT  '
  IF @pMaxReturned IS NOT NULL AND @pMaxReturned!='0' BEGIN
     SET @query=@query + ' TOP ' + @pMaxReturned + ' '
  END

  SET @query=@query + '
      [ColonneIdentifiant],
      [ColonneTexte],
      [ColonneNombre],
      [ColonneDate]
   FROM [dbo].[TableTest]
   WHERE 1=1'
       SET NOCOUNT ON;
  
 IF @FiltreId  IS NOT NULL BEGIN
    SET @query = @query + ' AND ColonneIdentifiant = @InColonneIdentifiant' 
END

IF LEN(@FiltreTexte)>0 BEGIN
   SET @query = @query + ' AND ColonneTexte LIKE @InColonneTexte'
END

IF @FiltreNombre IS NOT NULL BEGIN
    SET @query = @query + ' AND ColonneNombre = @InColonneNombre'
END

IF @FilterDate IS NOT NULL
   SET @query = @query + ' AND ColonneDate = @InColonneDate'

-- Le resultat de la requette est récupéré
INSERT INTO @FiltreTableReturn
exec sp_executesql @query ,N'@InColonneIdentifiant bigint,
                             @InColonneTexte AS nvarchar(100),
                             @InColonneNombre AS bigint,
                             @InColonneDate AS datetime',
                             @InColonneIdentifiant = @FiltreId,
                             @InColonneTexte = @FiltreTexte,
                             @InColonneNombre = @FiltreNombre,
                             @InColonneDate = @FilterDate;
     
SELECT * FROM @FiltreTableReturn

END

GO
La conception K2
tester
La procédure stockée apparaît ici !

Assurez-vous de rafraîchir le service object SQL afin de voir apparaître votre procédure stockée, puis allez dans votre K2 Designer.

  1. Créer le smartobject associé à la table SQL
    1. Dans l’onglet methods, modifier la méthode Get List pour la faire utiliser votre procédure stockée. Vous pouvez aussi ajouter une nouvelle méthode.
  2. Créer une vue liste qui utilise la méthode associée à la procédure stockée
    1. Ajouter des contrôles de saisie dans l’en-tête de la vue, afin de proposer vos filtres
  3. Ajouter les règles suivantes :
    1. Une règle GetList (sans event) qui exécute la méthode Get List de la procédure stockée avec les paramètre des contrôles de saisie
    2. Une règle par contrôle de saisie qui appelle la règle GetList

En images :

tester2
Remarquez la colonne method bindings
designer
La partie du haut peut être modéliser comme vous le souhaitez. Vous pouvez aussi utiliser deux vues différentes : une vue item et une vue list
designer2
Les règles à configurer.
L’utilisation

olivier

Chez K2 France depuis Février 2016, je suis à votre écoute au poste de Support Technique. A l'aise avec les bases de données. Je développe aussi en .NET afin de ne pas perdre la main! Vous pouvez me suivre sur Olivier Chatagnon.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.