1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
| SELECT CHAMP1
FROM
(
SELECT
champ1
FROM
( /* Attribution 1 Vente */
SELECT V_LST_TICKET.TCKDATE AS DATETICKET, V_LST_TICKET.TCKNUM AS TICKET, '00001' AS NUMLIGNE, concat('1', ';' , '167000', ';' ,/* Code magasin */
RIGHT(CAST(V_LST_TICKET.TCKDATE AS DATE), 2) , + substring(cast(CAST(V_LST_TICKET.TCKDATE AS date) AS char), 6, 2) + LEFT(CAST(V_LST_TICKET.TCKDATE AS DATE), 4), ';' ,/* Date */
'C1', ';' ,/* Code caisse */
RIGHT(REPLICATE('0', 10) + COALESCE (RIGHT(V_LST_TICKET.TCKNUM,6), ''), 10), ';' ,/* Numéro ticket */
LEFT(CAST(V_LST_TICKET.TCKDATE AS TIME), 2) + substring(cast(CAST(V_LST_TICKET.TCKDATE AS TIME) AS char), 4, 2), ';' ,/* Heure Transaction */
LEFT( ARTICLES.ARTCODE, 2 ) + SUBSTRING(ARTICLES.ARTCODE, 4,2) + SUBSTRING(ARTICLES.ARTCODE, 7,2), ';' ,/* Groupe Nomenclature */
RIGHT(ARTICLES.ARTCODE,16) , ';' ,/* Code Barre */
RIGHT( concat(REPLICATE('0', 7), substring( CONVERT(varchar, ABS(PIECEVENTELIGNES.PLVQTE)), 1, CHARINDEX( '.', CONVERT(varchar, ABS(PIECEVENTELIGNES.PLVQTE) ) )-1 ), substring( CONVERT(varchar, ABS(PIECEVENTELIGNES.PLVQTE)), CHARINDEX( '.', CONVERT(varchar, ABS(PIECEVENTELIGNES.PLVQTE) ) ) + 1, 2 ) ), 7), /* Quantité */ ';' ,
CASE
WHEN PIECEVENTELIGNES.PLVMNTNETHT > 0 THEN 'F'
WHEN PIECEVENTELIGNES.PLVMNTNETHT < 0 THEN 'A'
ELSE 'F'
END , ';' ,
'EUR', /* Devise */ ';' ,
RIGHT( concat(REPLICATE('0', 7), substring( CONVERT(varchar, ABS(PIECEVENTELIGNES.PLVMNTNETHT)* ( 1 + PIECEVENTELIGNES.TVATAUX ) ), 1,
CHARINDEX( '.', CONVERT(varchar, ABS(PIECEVENTELIGNES.PLVMNTNETHT)* ( 1 + PIECEVENTELIGNES.TVATAUX ) ) )-1 ),
substring( CONVERT(varchar, ABS(PIECEVENTELIGNES.PLVMNTNETHT)* ( 1 + PIECEVENTELIGNES.TVATAUX )),
CHARINDEX( '.', CONVERT(varchar, ABS(PIECEVENTELIGNES.PLVMNTNETHT)* ( 1 + PIECEVENTELIGNES.TVATAUX ) ) ) + 1, 2 ) ), 7), /* Prix unitaire ttc */ ';' ,
'0000000', /* Montant remise*/ ';' ,
'0', /* Points supplémentaires */ ';' ,
substring(PIECEVENTELIGNES.PLVDESIGNATION,1,30)/* Désignation article */
) AS Champ1
FROM V_LST_TICKET LEFT OUTER JOIN PIECEVENTELIGNES
ON (V_LST_TICKET.TCKID = PIECEVENTELIGNES.TCKID)
AND (V_LST_TICKET.PCVID = PIECEVENTELIGNES.PCVID), ARTICLES, TIERS
WHERE PIECEVENTELIGNES.ARTID = ARTICLES.ARTID AND V_LST_TICKET.TIRID = TIERS.TIRID
AND PIECEVENTELIGNES.PLVTYPE = 'L' AND LEFT( ARTICLES.ARTCODE, 5 ) <> 'FIDEL' AND LEFT(V_LST_TICKET.TCKNUM,2) = 'T_' AND LEN(RTRIM(TIERS.TIRIDCARTE)) <> 0
UNION /* Attribution 3 Chèque fidélité */
SELECT V_LST_TICKET.TCKDATE AS DATETICKET, V_LST_TICKET.TCKNUM AS TICKET, '00002' AS NUMLIGNE, concat('3', ';' , '167000', ';' ,/* Code magasin */
RIGHT(CAST(V_LST_TICKET.TCKDATE AS DATE), 2) , + substring(cast(CAST(V_LST_TICKET.TCKDATE AS date) AS char), 6, 2) + LEFT(CAST(V_LST_TICKET.TCKDATE AS DATE), 4), ';' ,/* Date */
'C1', ';' ,/* Code caisse */
RIGHT(REPLICATE('0', 10) + COALESCE (RIGHT(V_LST_TICKET.TCKNUM,6), ''), 10), ';' ,/* Numéro ticket */
LEFT(CAST(V_LST_TICKET.TCKDATE AS TIME), 2) + substring(cast(CAST(V_LST_TICKET.TCKDATE AS TIME) AS char), 4, 2), ';' ,/* Heure Transaction */
'000000', ';' ,/* Groupe Nomenclature */
'2888888021236', ';' ,/* Code Barre */
'0000000', /* Quantité */ ';' ,
CASE
WHEN PIECEVENTELIGNES.PLVMNTNETHT > 0 THEN 'F'
WHEN PIECEVENTELIGNES.PLVMNTNETHT < 0 THEN 'A'
ELSE 'F'
END , ';' ,
'EUR', /* Devise */ ';' ,
'0000000', /* Prix unitaire ttc */ ';' ,
'0000000', /* Montant remise*/ ';' ,
'0', /* Points supplémentaires */ ';' ,
substring(PIECEVENTELIGNES.PLVDESIGNATION,1,30) /* Désignation article */
) AS Champ1
FROM V_LST_TICKET LEFT OUTER JOIN PIECEVENTELIGNES
ON (V_LST_TICKET.TCKID = PIECEVENTELIGNES.TCKID)
AND (V_LST_TICKET.PCVID = PIECEVENTELIGNES.PCVID), ARTICLES
WHERE PIECEVENTELIGNES.ARTID = ARTICLES.ARTID AND PIECEVENTELIGNES.PLVTYPE = 'L' AND LEFT( ARTICLES.ARTCODE, 5 ) = 'FIDEL' AND LEFT(V_LST_TICKET.TCKNUM,2) = 'T_'
UNION /* Attribution 2 Signature de fin de ticket */
SELECT V_LST_TICKET.TCKDATE AS DATETICKET, V_LST_TICKET.TCKNUM AS TICKET, '00003' AS NUMLIGNE, concat('2', ';' , '167000', ';' ,/* Code magasin */
RIGHT(CAST(V_LST_TICKET.TCKDATE AS DATE), 2) , + substring(cast(CAST(V_LST_TICKET.TCKDATE AS date) AS char), 6, 2) + LEFT(CAST(V_LST_TICKET.TCKDATE AS DATE), 4), ';' ,/* Date */
'C1', ';' ,/* Code caisse */
RIGHT(REPLICATE('0', 10) + COALESCE (RIGHT(V_LST_TICKET.TCKNUM,6), ''), 10), ';' ,/* Numéro ticket */
LEFT(CAST(V_LST_TICKET.TCKDATE AS TIME), 2) + substring(cast(CAST(V_LST_TICKET.TCKDATE AS TIME) AS char), 4, 2), ';' ,/* Heure Transaction */
'', ';' ,/* Groupe Nomenclature */
TIERS.TIRIDCARTE, ';' ,/* Code Carte fidélité Client */
RIGHT( concat(REPLICATE('0', 7), substring( CONVERT(varchar, ABS(V_LST_TICKET.nbArticles)), 1, CHARINDEX( '.', CONVERT(varchar, ABS(V_LST_TICKET.nbArticles) ) )-1 ), substring( CONVERT(varchar, ABS(V_LST_TICKET.nbArticles)), CHARINDEX( '.', CONVERT(varchar, ABS(V_LST_TICKET.nbArticles) ) ) + 1, 2 ) ), 7), /* Nb Articles */ ';' ,
CASE
WHEN V_LST_TICKET.MontantTTC > 0 THEN 'F'
WHEN V_LST_TICKET.MontantTTC < 0 THEN 'A'
ELSE 'F'
END , ';' ,
'EUR', /* Devise */ ';' ,
RIGHT( concat(REPLICATE('0', 7), substring( CONVERT(varchar, ABS(V_LST_TICKET.MontantTTC) ), 1, CHARINDEX( '.', CONVERT(varchar, ABS(V_LST_TICKET.MontantTTC) ) )-1 ),
substring( CONVERT(varchar, ABS(V_LST_TICKET.MontantTTC)), CHARINDEX( '.', CONVERT(varchar, ABS(V_LST_TICKET.MontantTTC) ) ) + 1, 2 ) ), 7), /* Prix unitaire ttc */ ';' ,
'0000000', /* Montant remise*/ ';' ,
'0', /* Points supplémentaires */ ';' ,
''/* Vide */
) AS Champ1
From V_LST_TICKET, TIERS
WHERE V_LST_TICKET.TIRID = TIERS.TIRID AND LEFT(V_LST_TICKET.TCKNUM,2) = 'T_' AND LEN(RTRIM(TIERS.TIRIDCARTE)) <> 0
) VUE
WHERE CAST(DATETICKET AS DATE) = @date
) VUE1
UNION ALL/* Signature de fin de fichier */
SELECT TOP 1 CONCAT( '5167000_',left(@datetxt,4),substring(@datetxt,6,2),right(@datetxt,2),'.',LOWER('txt'),'_FIN' ) AS CHAMP1 |
Partager