It's a good start... but it still has work to do. Also when using wysiwyg in IE, you lose all the formatting anyways. At least I got a starting point now .
CREATE PROCEDURE [dbo].[SPNSearchAchats]
--recherche par rapport À l'entete
@NoCommande AS INT = NULL -- # de commande
, @NomFournisseur AS VARCHAR(50) = NULL -- nom du fournisseur
, @DateCommandeD AS DATETIME = NULL -- date dÉbut de la commande
, @DateCommandeF AS DATETIME = NULL -- date fin de la commande
, @DateLivraisonD AS DATETIME = NULL -- date dÉbut de la Livraison
, @DateLivraisonF AS DATETIME = NULL -- date fin de la Livraison
--recherche par rapport au piÈces
, @CodeDePiece AS VARCHAR(10) = NULL -- Code de la piÈce
, @DescPiece AS VARCHAR(50) = NULL -- description du produit
, @NoBonTravail AS VARCHAR(10) = NULL -- # bon travail oÙ envoyer la piÈce
, @PoClient AS VARCHAR(50) = NULL -- # de commande du client
, @NomClient AS VARCHAR(50) = NULL -- nom du client
, @NoContrat AS VARCHAR(10) = NULL -- # de contrat
, @Complete AS BIT = NULL -- Si la rÉception est complÈte
, @NoPackingSlip AS VARCHAR(50) = NULL -- # packing slip
, @NoFacture AS VARCHAR(50) = NULL -- # facture du fournisseur
, @NoBillTransport AS VARCHAR(50) = NULL -- # bil de transport
, @DateReceptionD AS DATETIME = NULL -- date dÉbut de la rÉception
, @DateReceptionF AS DATETIME = NULL -- date dÉbut de la rÉception
--paramÈtres pour le type de recherche
, @Location AS TINYINT = 0 -- Type recherche : au dÉbut du champ(1), n'importe oÙ (3), match exact (0)
, @OrderBy AS VARCHAR(15) = 'NoCommande' -- colonne choisie pour le tri
, @ALLCriterias AS BIT = 0 -- trouver tous les critÈres oÙ un seul
WITH RECOMPILE
AS
SET NOCOUNT ON
DECLARE @StartWC AS VARCHAR(1)
DECLARE @EndWC AS VARCHAR(1)
DECLARE @bAnyParamIsSet AS BIT
DECLARE @bAnyDateCommande AS BIT
DECLARE @bAnyDateLivraison AS BIT
DECLARE @bAnyDateReception AS BIT
DECLARE @bAnyEnteteParamIsSet AS BIT
DECLARE @bAnyPieceParamIsSet AS BIT
SELECT
@NoCommande = NULLIF(@NoCommande, '')
, @NomFournisseur = NULLIF(@NomFournisseur, '')
, @NomClient = NULLIF(@NomClient, '')
, @DateCommandeD = NULLIF(@DateCommandeD, '')
, @DateCommandeF = NULLIF(@DateCommandeF, '')
, @DateLivraisonD = NULLIF(@DateLivraisonD, '')
, @DateLivraisonF = NULLIF(@DateLivraisonF, '')
, @DateReceptionD = NULLIF(@DateReceptionD, '')
, @DateReceptionF = NULLIF(@DateReceptionF, '')
, @CodeDePiece = NULLIF(@CodeDePiece, '')
, @DescPiece = NULLIF(@DescPiece, '')
, @NoBonTravail = NULLIF(@NoBonTravail, '')
, @NoContrat = NULLIF(@NoContrat, '')
, @PoClient = NULLIF(@PoClient, '')
, @NoPackingSlip = NULLIF(@NoPackingSlip, '')
, @NoFacture = NULLIF(@NoFacture, '')
, @NoBillTransport = NULLIF(@NoBillTransport, '')
, @OrderBy = NULLIF(@OrderBy, 'NoCommande')
, @StartWC = CASE WHEN @Location & 2 = 2 THEN '%' ELSE '' END
, @EndWC = CASE WHEN @Location & 1 = 1 THEN '%' ELSE '' END
, @bAnyEnteteParamIsSet = CAST(LEN(
ISNULL(CAST(@NoCommande AS VARCHAR(10)), '')
+ ISNULL(@NomFournisseur, '')
+ ISNULL(CAST(@DateCommandeD AS VARCHAR(30)), '')
+ ISNULL(CAST(@DateCommandeF AS VARCHAR(30)), '')
+ ISNULL(CAST(@DateLivraisonD AS VARCHAR(30)), '')
+ ISNULL(CAST(@DateLivraisonF AS VARCHAR(30)), '')
  AS BIT)
, @bAnyPieceParamIsSet = CAST(LEN(
ISNULL(CAST(@Complete AS VARCHAR(1)),'')
+ ISNULL(@NomClient, '')
+ ISNULL(@CodeDePiece, '')
+ ISNULL(@DescPiece, '')
+ ISNULL(@NoBonTravail, '')
+ ISNULL(@NoContrat, '')
+ ISNULL(@PoClient, '')
+ ISNULL(@NoPackingSlip, '')
+ ISNULL(@NoFacture, '')
+ ISNULL(@NoBillTransport, '')
+ ISNULL(CAST(@DateReceptionD AS VARCHAR(30)), '')
+ ISNULL(CAST(@DateReceptionF AS VARCHAR(30)), '')
  AS BIT)
, @bAnyDateCommande = CAST(ISDATE(@DateCommandeD) + ISDATE(@DateCommandeF) AS BIT)
, @bAnyDateLivraison = CAST(ISDATE(@DateLivraisonD) + ISDATE(@DateLivraisonF) AS BIT)
, @bAnyDateReception = CAST(ISDATE(@DateReceptionD) + ISDATE(@DateReceptionF) AS BIT)
SET @bAnyParamIsSet = @bAnyEnteteParamIsSet | @bAnyPieceParamIsSet
SELECT
@DateCommandeD = ISNULL(@DateCommandeD, 0)
, @DateCommandeF = ISNULL(@DateCommandeF, DATEADD(D, 1, GETDATE()))
, @DateLivraisonD = ISNULL(@DateLivraisonD, 0)
, @DateLivraisonF = ISNULL(@DateLivraisonF, DATEADD(D, 1, GETDATE()))
, @DateReceptionD = ISNULL(@DateReceptionD, 0)
, @DateReceptionF = ISNULL(@DateReceptionF, DATEADD(D, 1, GETDATE()))
SELECT [NO DE COMMANDE]
, [NO DE CONTRAT]
, [Livre a]
, [Nom liv]
, FNOM
, [DATE DE COMMNANDE]
, LIVRAISON
, CONTACT
FROM dbo.ENTETE E
WHERE
@bAnyParamIsSet = 1 AND --allows sql server not to do any work if no param is set
0 <
CASE WHEN @ALLCriterias = 0 THEN --retourner la ligne si un seul des critÈres est valide
CASE
WHEN NOT @NoCommande IS NULL AND E.[NO DE COMMANDE] LIKE @StartWC + CONVERT(VARCHAR(10), @NoCommande) + @EndWC THEN 1
WHEN NOT @NomFournisseur IS NULL AND E.FNOM LIKE @StartWC + @NomFournisseur + @EndWC THEN 1
WHEN @bAnyDateCommande = 1 AND E.[DATE DE COMMNANDE] BETWEEN @DateCommandeD AND @DateCommandeF THEN 1
WHEN @bAnyDateLivraison = 1 AND E.LIVRAISON BETWEEN @DateLivraisonD AND @DateLivraisonF THEN 1
WHEN @bAnyPieceParamIsSet = 1 THEN
CASE WHEN EXISTS (
SELECT
*
FROM dbo.DETAIL D
WHERE D.[NO DE COMMANDE] = E.[NO DE COMMANDE]
AND @bAnyParamIsSet = 1
AND 0 <
CASE WHEN NOT @NoContrat IS NULL AND D.[NO DE CONTRAT] LIKE @StartWC + @NoContrat + @EndWC THEN 1
WHEN NOT @PoClient IS NULL AND D.[PO CLIENT] LIKE @StartWC + @PoClient + @EndWC THEN 1
WHEN NOT @NomClient IS NULL AND D.[Nom du Client] LIKE @StartWC + @NomClient + @EndWC THEN 1
WHEN NOT @CodeDePiece IS NULL AND D.PNUM LIKE @StartWC + @CodeDePiece + @EndWC THEN 1
WHEN NOT @DescPiece IS NULL AND D.PNOM LIKE @StartWC + @DescPiece + @EndWC THEN 1
WHEN NOT @NoBonTravail IS NULL AND D.FT LIKE @StartWC + @NoBonTravail + @EndWC THEN 1
WHEN NOT @NoPackingSlip IS NULL AND ',' + ISNULL([PSLIP 1], '') + ',' + ISNULL([PSLIP 2], '') + ',' + ISNULL([PSLIP 3], '') + ',' + ISNULL([PSLIP 4], '') + ',' + ISNULL([PSLIP 5], '') + ',' LIKE '%,' + @StartWC + @NoPackingSlip + @EndWC + ',%' THEN 1
WHEN NOT @NoFacture IS NULL AND ',' + ISNULL([FACTURE 1], '') + ',' + ISNULL([FACTURE 2], '') + ',' + ISNULL([FACTURE 3], '') + ',' + ISNULL([FACTURE 4], '') + ',' + ISNULL([FACTURE 5], '') + ',' LIKE '%,' + @StartWC + @NoFacture + @EndWC + ',%' THEN 1
WHEN NOT @NoBillTransport IS NULL AND',' + ISNULL([BILL TRANS 1], '') + ',' + ISNULL([BILL TRANS 2], '') + ',' + ISNULL([BILL TRANS 3], '') + ',' + ISNULL([BILL TRANS 4], '') + ',' + ISNULL([BILL TRANS 5], '') + ',' LIKE '%,' + @StartWC + @NoBillTransport + @EndWC + ',%' THEN 1
WHEN 0 <
CASE WHEN @Complete IS NULL THEN 0
WHEN @Complete = 0 AND QUANTITÉ > ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1
WHEN @Complete = 1 AND QUANTITÉ <= ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1
ELSE 0
END
THEN 1
WHEN 0 <
CASE WHEN @bAnyDateReception = 0 THEN 0
WHEN date_de_reception1 BETWEENN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception2 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception3 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception4 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception5 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
ELSE 0
END
THEN 1
ELSE 0
END
)
THEN 1
ELSE 0
END
ELSE 0
END
ELSE --@ALLCriterias = 1--retourner la ligne si TOUS les critÈres sont valides
CASE WHEN
(@NoCommande IS NULL OR E.[NO DE COMMANDE] LIKE @StartWC + CONVERT(VARCHAR(10), @NoCommande) + @EndWC )
AND (@NomFournisseur IS NULL OR E.FNOM LIKE @StartWC + @NomFournisseur + @EndWC )
AND (@bAnyDateCommande = 0 OR E.[DATE DE COMMNANDE] BETWEEN @DateCommandeD AND @DateCommandeF )
AND (@bAnyDateLivraison = 0 OR E.LIVRAISON BETWEEN @DateLivraisonD AND @DateLivraisonF )
AND 0 < CASE WHEN @bAnyPieceParamIsSet = 0 THEN 1
WHEN EXISTS (
SELECT
*
FROM dbo.DETAIL D
WHERE D.[NO DE COMMANDE] = E.[NO DE COMMANDE]
AND @bAnyParamIsSet = 1
AND 0 < CASE WHEN
(@NoContrat IS NULL OR D.[NO DE CONTRAT] LIKE @StartWC + @NoContrat + @EndWC )
AND (@PoClient IS NULL OR D.[PO CLIENT] LIKE @StartWC + @PoClient + @EndWC )
AND (@NomClient IS NULL OR D.[NOM DU CLIENT] LIKE @StartWC + @NomClient + @EndWC )
AND (@CodeDePiece IS NULL OR D.PNUM LIKE @StartWC + @CodeDePiece + @EndWC )
AND (@DescPiece IS NULL OR D.PNOM LIKE @StartWC + @DescPiece + @EndWC )
AND (@NoBonTravail IS NULL OR D.FT LIKE @StartWC + @NoBonTravail + @EndWC )
AND (@NoPackingSlip IS NULL OR ',' + ISNULL([PSLIP 1], '') + ',' + ISNULL([PSLIP 2], '') + ',' + ISNULL([PSLIP 3], '') + ',' + ISNULL([PSLIP 4], '') + ',' + ISNULL([PSLIP 5], '') + ',' LIKE '%,' + @StartWC + @NoPackingSlip + @EndWC + ',%' )
AND (@NoFacture IS NULL OR ',' + ISNULL([FACTURE 1], '') + ',' + ISNULL([FACTURE 2], '') + ',' + ISNULL([FACTURE 3], '') + ',' + ISNULL([FACTURE 4], '') + ',' + ISNULL([FACTURE 5], '') + ',' LIKE '%,' + @StartWC + @NoFacture + @EndWC + ',%' )
AND (@NoBillTransport IS NULL OR ',' + ISNULL([BILL TRANS 1], '') + ',' + ISNULL([BILL TRANS 2], '') + ',' + ISNULL([BILL TRANS 3], '') + ',' + ISNULL([BILL TRANS 4], '') + ',' + ISNULL([BILL TRANS 5], '') + ',' LIKE '%,' + @StartWC + @NoBillTransport + @EndWC + ',%' )
AND 0 <
CASE WHEN @Complete IS NULL THEN 1
WHEN @Complete = 0 AND QUANTITÉ > ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1
WHEN @Complete = 1 AND QUANTITÉ <= ([QTEE RECU 1] + [QTEE RECU 2] + [QTEE RECU 3] + [QTEE RECU 4] + [QTEE RECU 5]) THEN 1
ELSE 0
END
AND 0 <
CASE WHEN @bAnyDateReception = 0 THEN 1
WHEN date_de_reception1 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception2 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception3 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception4 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
WHEN date_de_reception5 BETWEEN @DateReceptionD AND @DateReceptionF THEN 1
ELSE 0
END
THEN 1
ELSE 0
END
) THEN 1
ELSE 0
END
THEN 1
ELSE 0
END
END
ORDER BY
CASE WHEN @OrderBy = 'NoCommande' THEN E.[NO DE COMMANDE] ELSE NULL END
, CASE WHEN @OrderBy = 'NoContrat' THEN E.[NO DE CONTRAT] ELSE NULL END
, CASE WHEN @OrderBy = 'NomFournisseur' THEN E.FNOM ELSE NULL END
, CASE WHEN @OrderBy = 'NomClient' THEN E.[Nom liv] ELSE NULL END
, CASE WHEN @OrderBy = 'DateCommande' THEN E.[DATE DE COMMNANDE] ELSE NULL END
, CASE WHEN @OrderBy = 'Contact' THEN E.CONTACT ELSE NULL END
, CASE WHEN @OrderBy = 'DateLivraison' THEN E.LIVRAISON ELSE NULL END
, CASE WHEN @OrderBy = 'NoClient' THEN E.[Livre a] ELSE NULL END
SET NOCOUNT OFF
GO