SELECT COUNT(*)
FROM (
SELECT [m].[Id], CASE
WHEN [t4].[Id] IS NULL THEN COALESCE([t4].[FirstName], [t5].[FirstName])
ELSE N''
END AS [CustomerFirstName], CASE
WHEN [t4].[Id] IS NULL THEN COALESCE([t4].[LastName], [t5].[LastName])
ELSE N''
END AS [CustomerLastName], CASE
WHEN [t4].[Id] IS NULL THEN COALESCE([t4].[Number], [t5].[Number])
ELSE N''
END AS [CustomerNumber], CASE
WHEN [t4].[Id] IS NULL THEN [t5].[Id]
ELSE [t4].[Id]
END AS [CustomerId], [m].[CreatedOn] AS [CreationDate], [m].[UpdatedOn], CAST(1 AS bit) AS [IsReal], [m].[FuturesPrice], [m].[IsSell], [m].[MarketAccount], [m].[WasAcknowledge], [m].[PassFill], [m].[IsGtc], [m].[Expiration], [m].[Lots], [m].[WorkingLots], [t].[ProductId], [t0].[Code] AS [InstrumentCode], [m].[FuturesMonth], CONVERT(nvarchar(max), [m].[MarketId]) AS [MarketId], CONVERT(nvarchar(max), [t1].[Name]) AS [AccountName], CONVERT(nvarchar(max), [m].[InternalCode]) AS [InternalCode], CASE
WHEN [m].[Source] = N'Contract' THEN 0
WHEN [m].[Source] = N'Accumulation' THEN 2
WHEN [m].[Source] = N'Offer' THEN 1
ELSE 3
END AS [Source], CASE
WHEN [m].[Type] = N'Market' THEN 0
ELSE 1
END AS [Type], CASE
WHEN [m].[Event] = N'Creation' THEN 0
WHEN [m].[Event] = N'Edition' THEN 2
WHEN [m].[Event] = N'Cancelation' THEN 1
WHEN [m].[Event] = N'Roll' THEN 3
WHEN [m].[Event] = N'Book' THEN 4
WHEN [m].[Event] = N'RollBack' THEN 5
WHEN [m].[Event] = N'PartiallyFilled' THEN 6
WHEN [m].[Event] = N'Filled' THEN 7
ELSE 8
END AS [Event], CASE
WHEN [m].[State] = N'Ready' THEN 0
WHEN [m].[State] = N'Denied' THEN 1
WHEN [m].[State] = N'Pending' THEN 2
WHEN [m].[State] = N'Working' THEN 3
WHEN [m].[State] = N'PartiallyFilled' THEN 4
WHEN [m].[State] = N'Filled' THEN 5
WHEN [m].[State] = N'Canceled' THEN 6
WHEN [m].[State] = N'Rejected' THEN 7
ELSE 8
END AS [State]
FROM [Transactions].[MarketTransaction] AS [m]
INNER JOIN (
SELECT [c].[Id], [c].[AutoHedge], [c].[BasisControl], [c].[CreatedBy], [c].[CreatedOn], [c].[CropEndDay], [c].[CropEndMonth], [c].[CropEndYear], [c].[CropStartDay], [c].[CropStartMonth], [c].[CropStartYear], [c].[IsActive], [c].[IsInSync], [c].[LotFactor], [c].[Name], [c].[Number], [c].[NumberOfCropYears], [c].[PriceControl], [c].[ProductId], [c].[TenantId], [c].[UpdatedBy], [c].[UpdatedOn]
FROM [Settings].[Commodity] AS [c]
WHERE TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536'
) AS [t] ON [m].[CommodityId] = [t].[Id]
INNER JOIN (
SELECT [p].[Id], [p].[Code], [p].[CreatedBy], [p].[CreatedOn], [p].[Description], [p].[ExchangeSymbol], [p].[FutureMonths], [p].[IsActive], [p].[IsInSync], [p].[LimitOrderPrefix], [p].[MarketOrderPrefix], [p].[Number], [p].[TenantId], [p].[UpdatedBy], [p].[UpdatedOn]
FROM [Settings].[Product] AS [p]
WHERE TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536'
) AS [t0] ON [t].[ProductId] = [t0].[Id]
LEFT JOIN (
SELECT [h].[Id], [h].[Account], [h].[CreatedBy], [h].[CreatedOn], [h].[IsActive], [h].[Name], [h].[TenantId], [h].[UpdatedBy], [h].[UpdatedOn]
FROM [Settings].[HedgeAccount] AS [h]
WHERE TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536'
) AS [t1] ON [m].[MarketAccount] = [t1].[Account]
LEFT JOIN (
SELECT [c0].[Id], [c0].[CashSettlement], [c0].[ChildCount], [c0].[ChildOffersCount], [c0].[Comments], [c0].[CommodityId], [c0].[ContractTypeId], [c0].[CreatedBy], [c0].[CreatedOn], [c0].[CropYear], [c0].[CustomerId], [c0].[DeliveryEndDate], [c0].[DeliveryLocationId], [c0].[DeliveryStartDate], [c0].[DoNotHedge], [c0].[EmployeeId], [c0].[ErpMessage], [c0].[ErpStatus], [c0].[Event], [c0].[Expiration], [c0].[ExtendedContractTypeId], [c0].[Fees1], [c0].[Fees2], [c0].[FreightPrice], [c0].[FuturesMonth], [c0].[FuturesPrice], [c0].[GrossRemainingBalance], [c0].[InternalCode], [c0].[IsActive], [c0].[IsDeliveryDatesCustom], [c0].[IsSell], [c0].[LastTransactionQuantity], [c0].[LocationId], [c0].[NetBasis], [c0].[Number], [c0].[OfferId], [c0].[ParentId], [c0].[PassFill], [c0].[PostedBasis], [c0].[Price], [c0].[PushBasis], [c0].[Quantity], [c0].[RealCropYear], [c0].[RemainingBalance], [c0].[RootParentId], [c0].[SeqId], [c0].[Source], [c0].[TenantId], [c0].[TheirContract], [c0].[TransactionTypeId], [c0].[UpdatedBy], [c0].[UpdatedEmployeeId], [c0].[UpdatedOn]
FROM [Transactions].[Contract] AS [c0]
WHERE TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536'
) AS [t2] ON [m].[ContractId] = [t2].[Id]
LEFT JOIN (
SELECT [o].[Id], [o].[CashSettlement], [o].[ChangeRemainingBalanceOnProcess], [o].[Comments], [o].[CommodityId], [o].[ContractParentId], [o].[ContractTypeId], [o].[CreatedBy], [o].[CreatedOn], [o].[CropYear], [o].[CustomerId], [o].[DeliveryEndDate], [o].[DeliveryLocationId], [o].[DeliveryStartDate], [o].[EmployeeId], [o].[Event], [o].[Expiration], [o].[Fees1], [o].[Fees2], [o].[FillCount], [o].[FreightPrice], [o].[FuturesMonth], [o].[FuturesPrice], [o].[Gtc], [o].[HasRejection], [o].[Instrument], [o].[InternalCode], [o].[IsActive], [o].[IsDeliveryDatesCustom], [o].[IsInternal], [o].[IsOrphan], [o].[IsSell], [o].[LocationId], [o].[NetBasis], [o].[Number], [o].[PostedBasis], [o].[Price], [o].[PushBasis], [o].[Quantity], [o].[RealCropYear], [o].[RemainingBalance], [o].[RemainingBalanceOnProcess], [o].[TenantId], [o].[TheirContract], [o].[TransactionTypeId], [o].[UpdatedBy], [o].[UpdatedEmployeeId], [o].[UpdatedOn]
FROM [Transactions].[Offer] AS [o]
WHERE  TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536'
) AS [t3] ON [m].[OfferId] = [t3].[Id]
LEFT JOIN (
SELECT [c1].[Id], [c1].[City], [c1].[Country], [c1].[CreatedBy], [c1].[CreatedOn], [c1].[Email], [c1].[FirstName], [c1].[IsActive], [c1].[IsInSync], [c1].[IsMobileAppEnable], [c1].[LastName], [c1].[MobileAppLimit], [c1].[Number], [c1].[PhoneNumber], [c1].[State], [c1].[Street], [c1].[TenantId], [c1].[UpdatedBy], [c1].[UpdatedOn], [c1].[WorkPhoneNumber], [c1].[ZipCode]
FROM [Settings].[Customer] AS [c1]
WHERE  TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536'
) AS [t4] ON [t2].[CustomerId] = [t4].[Id]
LEFT JOIN (
SELECT [c2].[Id], [c2].[City], [c2].[Country], [c2].[CreatedBy], [c2].[CreatedOn], [c2].[Email], [c2].[FirstName], [c2].[IsActive], [c2].[IsInSync], [c2].[IsMobileAppEnable], [c2].[LastName], [c2].[MobileAppLimit], [c2].[Number], [c2].[PhoneNumber], [c2].[State], [c2].[Street], [c2].[TenantId], [c2].[UpdatedBy], [c2].[UpdatedOn], [c2].[WorkPhoneNumber], [c2].[ZipCode]
FROM [Settings].[Customer] AS [c2]
WHERE TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536'
) AS [t5] ON [t3].[CustomerId] = [t5].[Id]
WHERE ([m].TenantId  =  'D60EBE3B-2EA2-4D40-9226-27E9C420D536') AND (([m].[IsActive] = CAST(1 AS bit)) AND (([m].[Source] = N'ExternalFill') OR EXISTS (
SELECT 1
FROM [Settings].[LocationEmployee] AS [l]
INNER JOIN (
SELECT [e].[Id], [e].[CreatedBy], [e].[CreatedOn], [e].[Email], [e].[FirstName], [e].[IsActive], [e].[IsInSync], [e].[LastName], [e].[Number], [e].[Role], [e].[Tag50], [e].[Tag50Account], [e].[TenantId], [e].[UpdatedBy], [e].[UpdatedOn]
FROM [Settings].[Employee] AS [e]
WHERE ( TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536')
) AS [t6] ON [l].[EmployeeId] = [t6].[Id]
WHERE ((( [t6].TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536')) AND ((([t6].[Email] = '[email protected]') AND ([l].[IsActive] = CAST(1 AS bit))) AND (([l].[CanBuy] = CAST(1 AS bit)) OR ([l].[CanSell] = CAST(1 AS bit))))) AND (([l].[LocationId] = [t2].[DeliveryLocationId]) OR ([l].[LocationId] = [t3].[DeliveryLocationId])))))
UNION
SELECT [o0].[Id], [t11].[FirstName] AS [CustomerFirstName], [t11].[LastName] AS [CustomerLastName], [t11].[Number] AS [CustomerNumber], [t11].[Id] AS [CustomerId], [o0].[CreatedOn] AS [CreationDate], [o0].[UpdatedOn], CAST(0 AS bit) AS [IsReal], COALESCE([t8].[FuturesPrice], 0.0) AS [FuturesPrice], CASE
WHEN [t8].[IsSell] = CAST(0 AS bit) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [IsSell], 0 AS [MarketAccount], CAST(0 AS bit) AS [WasAcknowledge], CAST(0 AS bit) AS [PassFill], [t8].[Gtc] AS [IsGtc], [t8].[Expiration], CAST(([t8].[Quantity] / CAST([t9].[LotFactor] AS decimal(18,2))) AS int) AS [Lots], 0 AS [WorkingLots], [t9].[ProductId], [t10].[Code] AS [InstrumentCode], [t8].[FuturesMonth], NULL AS [MarketId], NULL AS [AccountName], CONVERT(nvarchar(max), [t8].[InternalCode]) AS [InternalCode], 1 AS [Source], 1 AS [Type], CASE
WHEN [o0].[Action] = N'Cancel' THEN 1
WHEN [o0].[Action] = N'Add' THEN 0
ELSE 2
END AS [Event], 2 AS [State]
FROM [Transactions].[OfferMonitoring] AS [o0]
INNER JOIN (
SELECT [o1].[Id], [o1].[CashSettlement], [o1].[ChangeRemainingBalanceOnProcess], [o1].[Comments], [o1].[CommodityId], [o1].[ContractParentId], [o1].[ContractTypeId], [o1].[CreatedBy], [o1].[CreatedOn], [o1].[CropYear], [o1].[CustomerId], [o1].[DeliveryEndDate], [o1].[DeliveryLocationId], [o1].[DeliveryStartDate], [o1].[EmployeeId], [o1].[Event], [o1].[Expiration], [o1].[Fees1], [o1].[Fees2], [o1].[FillCount], [o1].[FreightPrice], [o1].[FuturesMonth], [o1].[FuturesPrice], [o1].[Gtc], [o1].[HasRejection], [o1].[Instrument], [o1].[InternalCode], [o1].[IsActive], [o1].[IsDeliveryDatesCustom], [o1].[IsInternal], [o1].[IsOrphan], [o1].[IsSell], [o1].[LocationId], [o1].[NetBasis], [o1].[Number], [o1].[PostedBasis], [o1].[Price], [o1].[PushBasis], [o1].[Quantity], [o1].[RealCropYear], [o1].[RemainingBalance], [o1].[RemainingBalanceOnProcess], [o1].[TenantId], [o1].[TheirContract], [o1].[TransactionTypeId], [o1].[UpdatedBy], [o1].[UpdatedEmployeeId], [o1].[UpdatedOn]
FROM [Transactions].[Offer] AS [o1]
WHERE ( TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536')
) AS [t8] ON [o0].[OfferId] = [t8].[Id]
INNER JOIN (
SELECT [c3].[Id], [c3].[AutoHedge], [c3].[BasisControl], [c3].[CreatedBy], [c3].[CreatedOn], [c3].[CropEndDay], [c3].[CropEndMonth], [c3].[CropEndYear], [c3].[CropStartDay], [c3].[CropStartMonth], [c3].[CropStartYear], [c3].[IsActive], [c3].[IsInSync], [c3].[LotFactor], [c3].[Name], [c3].[Number], [c3].[NumberOfCropYears], [c3].[PriceControl], [c3].[ProductId], [c3].[TenantId], [c3].[UpdatedBy], [c3].[UpdatedOn]
FROM [Settings].[Commodity] AS [c3]
WHERE  TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536'
) AS [t9] ON [t8].[CommodityId] = [t9].[Id]
INNER JOIN (
SELECT [p0].[Id], [p0].[Code], [p0].[CreatedBy], [p0].[CreatedOn], [p0].[Description], [p0].[ExchangeSymbol], [p0].[FutureMonths], [p0].[IsActive], [p0].[IsInSync], [p0].[LimitOrderPrefix], [p0].[MarketOrderPrefix], [p0].[Number], [p0].[TenantId], [p0].[UpdatedBy], [p0].[UpdatedOn]
FROM [Settings].[Product] AS [p0]
WHERE ( TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536')
) AS [t10] ON [t9].[ProductId] = [t10].[Id]
INNER JOIN (
SELECT [c4].[Id], [c4].[City], [c4].[Country], [c4].[CreatedBy], [c4].[CreatedOn], [c4].[Email], [c4].[FirstName], [c4].[IsActive], [c4].[IsInSync], [c4].[IsMobileAppEnable], [c4].[LastName], [c4].[MobileAppLimit], [c4].[Number], [c4].[PhoneNumber], [c4].[State], [c4].[Street], [c4].[TenantId], [c4].[UpdatedBy], [c4].[UpdatedOn], [c4].[WorkPhoneNumber], [c4].[ZipCode]
FROM [Settings].[Customer] AS [c4]
WHERE ( TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536')
) AS [t11] ON [t8].[CustomerId] = [t11].[Id]
WHERE (( [t11].TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536')) AND (([o0].[Action] = N'Add') AND EXISTS (
SELECT 1
FROM [Settings].[LocationEmployee] AS [l0]
INNER JOIN (
SELECT [e0].[Id], [e0].[CreatedBy], [e0].[CreatedOn], [e0].[Email], [e0].[FirstName], [e0].[IsActive], [e0].[IsInSync], [e0].[LastName], [e0].[Number], [e0].[Role], [e0].[Tag50], [e0].[Tag50Account], [e0].[TenantId], [e0].[UpdatedBy], [e0].[UpdatedOn]
FROM [Settings].[Employee] AS [e0]
WHERE ( TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536')
) AS [t12] ON [l0].[EmployeeId] = [t12].[Id]
WHERE ((( [t12].TenantId = 'D60EBE3B-2EA2-4D40-9226-27E9C420D536')) AND ((([t12].[Email] = '[email protected]') AND ([l0].[IsActive] = CAST(1 AS bit))) AND (([l0].[CanBuy] = CAST(1 AS bit)) OR ([l0].[CanSell] = CAST(1 AS bit))))) AND ([t8].[LocationId] = [l0].[LocationId])))
) AS [t7]
WHERE CASE
WHEN [t7].[UpdatedOn] IS NULL THEN CASE
WHEN CONVERT(date, [t7].[CreationDate]) <= '2023-07-03' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END
ELSE CASE
WHEN CONVERT(date, [t7].[UpdatedOn]) <= '2023-07-03' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END
END = CAST(1 AS bit)