ReportArticles0.733098cmtrueGroupe de produitstruetrue0.494371cmtrueGroupe d'articlestrueQuanttrueMontant0.514265cmtrue=Fields!Title.Valuetrue=Fields!Quantity.Valuetrue=Fields!Amount.Value3cm3.729632cm1.512015cm1.753936cm0.526201cmtrue=Fields!TaxRate.Valuetrue=SUm(Fields!Quantity.Value)true=Sum(Fields!Amount.Value)=Fields!TaxRate.Value5.098416cm7cm4
ReportPayments0.820776cmtruePaiementstruetruetrue0.5825897cmtrueTextBox70Mode de paiementtruetrueTextBox71CourstrueTextBox72MontantAscending=Fields!Sort.Value0.611656cmtrue=Fields!Title.Valuetrue=Fields!AmountCurrency.Value=Fields!Amount.Value=Fields!AmountCurrency.Valuetrue=Fields!ExchangeRate.Value=Fields!ExchangeRate.Value=1true=Fields!Amount.Value4cm2.983246cm1.199076cm1.054479cm1.841726cm0.597666cm2true=Fields!TransactionType.Value2true=IIF(Sum(Fields!Amount.Value)=0,"",Sum(Fields!Amount.Value))=Fields!TransactionType.ValueAscending=Fields!PaymentGroup.Value0.579484cm2true=IIF(Fields!PaymentGroup.Value=0,"Espèces",IIF(Fields!PaymentGroup.Value=1,"Paiements par carte",IIF(Fields!PaymentGroup.Value=3,"Autres obligations ",IIF(Fields!PaymentGroup.Value=4,"Charges de compte","Autres"))))2true=Sum(Fields!Amount.Value)=Fields!PaymentGroup.ValueAscending=Fields!Sort.Value8.098416cm7.078527cm5
0.4cm29.7cm7.8cm7.078527cmStringTransactionIddata source=sxzimtb96b.database.windows.net;multipleactiveresultsets=true;user id=arcavis;initial catalog=arcavis_integration;password=lamfQ1odI96MxSgc6sQYLCHf;SQLEodUserNameReportTitleFromDateToDateCurrentTimeTitleUserIdRealNamePosNameTransCountAnnCountAmountDECLARE @FromDate datetime
DECLARE @ToDate datetime
DECLARE @PointOfSaleId int
DECLARE @UserId int
DECLARE @EodTransactionId BIGINT
DECLARE @EodTransactionTypeId INT
DECLARE @Title VARCHAR(100)
DECLARE @UserEod int =-1,
@PosEod int =-1
SELECT @UserEod = JSON_VALUE(ExtJson,'$.UserEndOfDayTransactionTypeId'),
@PosEod = JSON_VALUE(ExtJson,'$.PosEndOfDayTransactionTypeId')
FROM [Configuration]
IF @PosEod IS NOT NULL
BEGIN
SET @EodTransactionTypeId = @PosEod
SELECT @ToDate = SaleTime,
@PointOfSaleId = PointOfSaleId,
@UserId=-1,
@Title = CASE
WHEN TransactionTypeId = @PosEod
THEN 'Arrêté de caisse (Z-Bon)'
ELSE 'Arrêté de caisse (X-Bon)'
END,
@EodTransactionId = CASE WHEN TransactionTypeId = @PosEod THEN Id ELSE NULL END
FROM Transactions
WHERE Id = @TransactionId
SELECT TOP 1
@FromDate=SaleTime
FROM Transactions
WHERE SaleTime < @ToDate
AND TransactionTypeId = @PosEod
AND PointOfSaleId = @PointOfSaleId
ORDER BY SaleTime DESC
END
ELSE
BEGIN
SET @EodTransactionTypeId = @UserEod
SELECT @ToDate = SaleTime,
@UserId = UserId,
@PointOfSaleId = -1,
@Title = CASE
WHEN TransactionTypeId = @PosEod
THEN 'Arrêté de caisse (Z-Bon)'
ELSE 'Arrêté de caisse (X-Bon)'
END,
@EodTransactionId = CASE WHEN TransactionTypeId = @PosEod THEN Id ELSE NULL END
FROM Transactions
WHERE Id = @TransactionId
SELECT TOP 1
@FromDate=SaleTime
FROM Transactions
WHERE SaleTime < @ToDate
AND TransactionTypeId = @UserEod
AND UserId = @UserId
ORDER BY SaleTime DESC
END
SELECT ISNULL(EOD.Username, U.Username) AS EodUserName
,RTRIM(@Title + ' ' + ISNULL(CONVERT(VARCHAR, @PointOfSaleId) + '/' + CONVERT(VARCHAR, CONVERT(INT, RIGHT(@EodTransactionId, 12))), '')) ReportTitle
,@FromDate AS FromDate
,@ToDate AS ToDate
,CONVERT(DATETIME, CONVERT(DATETIME, GETUTCDATE(), 104) AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time') CurrentTime
,TT.Title_fr AS Title
,T.UserId
,RealName
,CASE
WHEN @PointOfSaleId < 0
THEN U.RealName
ELSE P.Name
END AS PosName
,COUNT(DISTINCT CASE
WHEN AnnulationTransactionId < T.Id
THEN NULL
ELSE T.Id
END) AS TransCount
,COUNT(DISTINCT CASE
WHEN AnnulationTransactionId < T.Id
THEN T.Id
ELSE NULL
END) AS AnnCount
,SUM(Amount) AS Amount
FROM Transactions T
INNER JOIN TransactionTypes AS TT ON T.TransactionTypeId = TT.Id
INNER JOIN PointOfSales P ON T.PointOfSaleId = P.Id
INNER JOIN Users U ON U.Id = T.UserId
LEFT OUTER JOIN (
SELECT T.Id
,UserName
FROM Transactions T
INNER JOIN Users U ON U.Id = T.UserId
WHERE T.Id = @EodTransactionId
) EOD ON EOD.Id = @EodTransactionId
WHERE
-- Exclude eod transaction type
T.TransactionTypeId <> @EodTransactionTypeId
AND
-- select timerange
(
T.ExecutedTime > @FromDate
OR @FromDate IS NULL
)
AND (
@ToDate IS NULL
OR T.ExecutedTime <= @ToDate
)
AND (
T.PointOfSaleId = @PointOfSaleId
OR @PointOfSaleId < 0
)
AND (
T.UserId = @UserId
OR @UserId < 0
)
GROUP BY TT.Title_fr
,T.UserId
,RealName
,CASE
WHEN @PointOfSaleId < 0
THEN U.RealName
ELSE P.Name
END
,ISNULL(EOD.Username, U.Username)Arcavis=Parameters!TransactionId.ValueTitleTaxRateQuantityDiscountNetAmountAmountDECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
DECLARE @PointOfSaleId INT
DECLARE @UserId INT
DECLARE @UserEod INT = - 1
,@PosEod INT = - 1
SELECT @UserEod = JSON_VALUE(ExtJson, '$.UserEndOfDayTransactionTypeId')
,@PosEod = JSON_VALUE(ExtJson, '$.PosEndOfDayTransactionTypeId')
FROM [Configuration]
IF @PosEod IS NOT NULL
BEGIN
SELECT @ToDate = SaleTime,
@PointOfSaleId = PointOfSaleId,
@UserId=-1
FROM Transactions
WHERE Id = @TransactionId
SELECT TOP 1
@FromDate=SaleTime
FROM Transactions
WHERE SaleTime < @ToDate
AND TransactionTypeId = @PosEod
AND PointOfSaleId = @PointOfSaleId
ORDER BY SaleTime DESC
END
ELSE
BEGIN
SELECT @ToDate = SaleTime,
@UserId = UserId,
@PointOfSaleId = -1
FROM Transactions
WHERE Id = @TransactionId
SELECT TOP 1
@FromDate=SaleTime
FROM Transactions
WHERE SaleTime < @ToDate
AND TransactionTypeId = @UserEod
AND UserId = @UserId
ORDER BY SaleTime DESC
END
SELECT ISNULL(G2.Title_fr,G2.Title_de) Title
,TA.TaxRate
,SUM(TA.Quantity) AS Quantity
,ISNULL(SUM(Discount), 0) AS Discount
,ISNULL(SUM(TA.NetPrice), 0) AS NetAmount
,ISNULL(SUM(TA.Price), 0) AS Amount
FROM Transactions T
INNER JOIN TransactionTypes TT ON TT.Id = T.TransactionTypeId
INNER JOIN TransactionArticles TA ON T.Id = TA.TransactionId
INNER JOIN Articles A ON TA.ArticleId = A.Id
INNER JOIN ArticleGroups AS G ON A.ArticleGroupId = G.Id
INNER JOIN ArticleGroups G1 ON G1.Id = G.ParentGroupId
INNER JOIN ArticleGroups G2 ON G2.Id = G1.ParentGroupId
WHERE TT.SalesRelated = 1
AND (
T.SaleTime > @FromDate
OR @FromDate IS NULL
)
AND (
@ToDate IS NULL
OR T.SaleTime <= @ToDate
)
AND (
T.PointOfSaleId = @PointOfSaleId
OR @PointOfSaleId < 0
)
AND (
T.UserId = @UserId
OR @UserId < 0
)
GROUP BY TA.TaxRate
,ISNULL(G2.Title_fr,G2.Title_de)Arcavis=Parameters!TransactionId.ValueTransactionTypeSortPaymentGroupTitleExchangeRateAmountAmountCurrencyDECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
DECLARE @PointOfSaleId INT
DECLARE @UserId INT
DECLARE @UserEod INT = - 1
,@PosEod INT = - 1
SELECT @UserEod = JSON_VALUE(ExtJson, '$.UserEndOfDayTransactionTypeId')
,@PosEod = JSON_VALUE(ExtJson, '$.PosEndOfDayTransactionTypeId')
FROM [Configuration]
IF @PosEod IS NOT NULL
BEGIN
SELECT @ToDate = SaleTime
,@PointOfSaleId = PointOfSaleId
,@UserId=-1
FROM Transactions
WHERE Id = @TransactionId
SELECT TOP 1
@FromDate=SaleTime
FROM Transactions
WHERE SaleTime < @ToDate
AND TransactionTypeId = @PosEod
AND PointOfSaleId = @PointOfSaleId
ORDER BY SaleTime DESC
END
ELSE
BEGIN
SELECT @ToDate = SaleTime
,@UserId = UserId
,@PointOfSaleId=-1
FROM Transactions
WHERE Id = @TransactionId
SELECT TOP 1
@FromDate=SaleTime
FROM Transactions
WHERE SaleTime < @ToDate
AND TransactionTypeId = @UserEod
AND UserId = @UserId
ORDER BY SaleTime DESC
END
SELECT UPPER(TT.Title_fr + CASE
WHEN TransactionPurposeId IS NOT NULL
THEN ' - ' + ISNULL(TP.Title_fr, '')
ELSE ''
END) AS TransactionType
,TT.Sort
,CASE
WHEN AT.Debit = 1
AND P.Debit = 1
THEN 3
WHEN PT.IsCash = 1
THEN 0
WHEN PT.PaymentProcessing = 1
THEN 1
ELSE 2
END AS PaymentGroup
,ISNULL(CT.Name, PT.Title_fr) AS Title
,P.ExchangeRate
,SUM(CASE
WHEN P.Debit = 1
THEN - 1 * P.Amount * P.ExchangeRate
ELSE P.Amount * P.ExchangeRate
END) AS Amount
,SUM(CASE
WHEN P.Debit = 1
THEN - 1 * P.Amount
ELSE P.Amount
END) AS AmountCurrency
FROM Transactions AS T
INNER JOIN TransactionTypes TT ON TT.Id = TransactionTypeId
INNER JOIN TransactionPayments AS P ON P.TransactionId = T.Id
INNER JOIN PaymentTypes AS PT ON PT.Id = P.PaymentTypeId
INNER JOIN AccountTypes AS AT ON AT.Id = PT.AccountTypeId
LEFT OUTER JOIN TransactionPurposes TP ON TP.Id = T.TransactionPurposeId
LEFT OUTER JOIN EftCardTypes AS CT ON CT.CardType = P.EftCardType
WHERE NOT EXISTS (
SELECT *
FROM Configuration
WHERE PosTakeoutTransactionTypeId = T.TransactionTypeId
)
AND (
T.SaleTime > @FromDate
OR @FromDate IS NULL
)
AND (
@ToDate IS NULL
OR T.SaleTime <= @ToDate
)
AND (
T.PointOfSaleId = @PointOfSaleId
OR @PointOfSaleId < 0
)
AND (
T.UserId = @UserId
OR @UserId < 0
)
GROUP BY TT.Title_fr
,TransactionPurposeId
,TP.Title_fr
,TT.Sort
,ISNULL(CT.Name, PT.Title_fr)
,P.ExchangeRate
,CASE
WHEN AT.Debit = 1
AND P.Debit = 1
THEN 3
WHEN PT.IsCash = 1
THEN 0
WHEN PT.PaymentProcessing = 1
THEN 1
ELSE 2
ENDArcavis=Parameters!TransactionId.ValueCurrencyIsoCodeTakeOutBalanceDECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
DECLARE @PointOfSaleId INT
DECLARE @UserId INT
DECLARE @extJson VARCHAR(max)
-- Set user and posid
DECLARE @UserEod INT = - 1
,@PosEod INT = - 1
SELECT @UserEod = JSON_VALUE(ExtJson, '$.UserEndOfDayTransactionTypeId')
,@PosEod = JSON_VALUE(ExtJson, '$.PosEndOfDayTransactionTypeId')
FROM [Configuration]
IF @PosEod IS NOT NULL
BEGIN
SELECT @ToDate = SaleTime
,@PointOfSaleId = PointOfSaleId
,@UserId=-1
,@extJson = ExtJson
FROM Transactions
WHERE Id = @TransactionId
SELECT TOP 1
@FromDate=SaleTime
FROM Transactions
WHERE SaleTime < @ToDate
AND TransactionTypeId = @PosEod
AND PointOfSaleId = @PointOfSaleId
ORDER BY SaleTime DESC
END
ELSE
BEGIN
SELECT @ToDate = SaleTime
,@UserId = UserId
,@PointOfSaleId=-1
,@extJson = ExtJson
FROM Transactions
WHERE Id = @TransactionId
SELECT TOP 1
@FromDate=SaleTime
FROM Transactions
WHERE SaleTime < @ToDate
AND TransactionTypeId = @UserEod
AND UserId = @UserId
ORDER BY SaleTime DESC
END
SELECT C.CurrencyIsoCode
,ISNULL(SUM(Amount), 0) AS TakeOut
,CONVERT(decimal(19,4),ISNULL(JSON_VALUE(@extJson, '$.PosCashRegisters.' + C.CurrencyIsoCode), Balance)) Balance
FROM PosCashRegisters R
INNER JOIN Currencies C ON R.CurrencyId = C.Id
LEFT OUTER JOIN (
SELECT PointOfSaleId
,UserId
,CurrencyId
,SUM(CASE
WHEN Debit = 1
THEN P.Amount * - 1
ELSE P.Amount
END) AS Amount
FROM Transactions T
INNER JOIN Configuration ON PosTakeoutTransactionTypeId = T.TransactionTypeId
INNER JOIN TransactionPayments P ON P.TransactionId = T.Id
INNER JOIN PaymentTypes PT ON PT.Id = P.PaymentTypeId
WHERE (
T.SaleTime > @FromDate
OR @FromDate IS NULL
)
AND (
@ToDate IS NULL
OR T.SaleTime <= @ToDate
)
AND PT.IsCash = 1
AND (
T.PointOfSaleId = @PointOfSaleId
OR @PointOfSaleId < 0
)
AND (
T.UserId = @UserId
OR @UserId < 0
)
GROUP BY PointOfSaleId
,UserId
,CurrencyId
) AS T ON (
T.PointOfSaleId = R.PointOfSaleId
OR T.UserId = R.UserId
)
AND R.CurrencyId = T.CurrencyId
WHERE (
R.PointOfSaleId = @PointOfSaleId
OR @PointOfSaleId < 0
)
AND (
R.UserId = @UserId
OR @UserId < 0
)
GROUP BY C.CurrencyIsoCode
,BalanceArcavis=Parameters!TransactionId.Value