-- To be imported
--Forward Curves



-- All counterparties
SELECT 
	C.Id AS SupplierId,
    C.Name,
    C.Description,
    C.Address,
    C.City,
    C.ZipCode,
    C.Type,
    C2.CountryName,
    L.LocationName
FROM counterpart.Company AS C
LEFT  JOIN geo.Location AS L ON C.LocationId = L.Id
LEFT  JOIN geo.Country AS C2 ON C.CountryId = C2.Id


-- All counterparties for import
SELECT 
	--ROW_NUMBER() OVER (ORDER BY C.name) AS code,
    C.Name,
	'' AS tax_identifier,
	'' AS vat_code,
    '' AS address_name,
	CASE REPLACE(LTRIM(RTRIM(C.Address)),'"','')
		WHEN '0' THEN ''
		WHEN '.' THEN '' 
		WHEN 'a' THEN '' 
		ELSE REPLACE(LTRIM(RTRIM(C.Address)),'"','') END 
		AS street,
    ISNULL(L.LocationName,'')	AS city,
    C.ZipCode AS zip,
	C2.IsoCode2 AS country_code,
	'' AS subdivision_code
FROM counterpart.Company AS C
LEFT  JOIN geo.Location AS L ON C.LocationId = L.Id
LEFT  JOIN geo.Country AS C2 ON C.CountryId = C2.Id
ORDER BY c.Name


-- Supplier used in purchase contracts
SELECT DISTINCT
	C.Id AS SupplierId,
    C.Name,
    C.Description,
    C.Address,
    C.City,
    C.ZipCode,
    C.Type,
    C2.CountryName,
    L.LocationName
FROM dbo.Deal AS D
LEFT  JOIN counterpart.Company		AS C	ON D.CounterpartId	= C.Id
LEFT  JOIN geo.Location AS L ON C.LocationId = L.Id
LEFT  JOIN geo.Country AS C2 ON C.CountryId = C2.Id
WHERE 1=1
  AND D.Status <> 'Cancelled'
  AND D.BuyOrSell = 1 


  
-- Customers used in sale contracts
SELECT DISTINCT
	C.Id AS CustomerId,
    C.Name,
    C.Description,
    C.Address,
    C.City,
    C.ZipCode,
    C.Type,
    C2.CountryName,
    L.LocationName
FROM dbo.Deal AS D
LEFT  JOIN counterpart.Company		AS C	ON D.CounterpartId	= C.Id
LEFT  JOIN geo.Location AS L ON C.LocationId = L.Id
LEFT  JOIN geo.Country AS C2 ON C.CountryId = C2.Id
WHERE 1=1
  AND D.Status <> 'Cancelled'
  AND D.BuyOrSell <> 1 


-- Type de produits tradé -> Desk
SELECT 
	D.Id AS DeskId,
	D.Caption AS Desk,
	D.Description AS Description,
	D.DeskType,
	D.IsDefaultDesk,
	D.DefaultUnitReference,
	D.ActualMT,
	D.QuantityPrecision,
	D.ActualM3,
	D.MT2BBLPrecision
FROM profiles.Desk AS D


-- Book: utilisé pour classer les deals FY (projection)
SELECT 
	B.Id	AS BookId,
	B.Caption AS Book,
	B.Description AS Description,
	D.Caption AS Desk,
	B.Year,
	B.IsActive
FROM Book AS B
INNER JOIN profiles.Desk AS D ON B.DeskId = D.Id
ORDER BY B.Year


-- Lien entre company (supplier, client...) et le produits tradé
SELECT 
	C.Name,
	D.Caption
FROM dbo.CompanyDeskAndTradingUnit AS CDATU
INNER JOIN counterpart.Company AS C ON CDATU.CompanyId = C.Id
INNER JOIN profiles.Desk AS D ON CDATU.DeskId = D.Id


-- Users
SELECT 
	XU.Id AS UserId,
	XU.FirstName,
	XU.LastName,
	XU.Login AS LoginName,	
	D.Caption AS Desk,
	D.Description AS DeskDescription,
	P.Caption AS ProfileName,
	P.Description AS ProfileDescription,
	XU.IsAdmin,
	XU.IsActive,
	XU.IsSystemAdministrator
FROM profiles.XUser AS XU
INNER JOIN profiles.Desk AS D ON XU.DeskId = D.Id
INNER JOIN profiles.Profile AS P ON XU.ProfileId = P.Id
WHERE 1=1
  AND XU.Login NOT IN ('Sylvain.Duvernay','Nicolas.Vignon', 'Walter.Marques', 'Katia.Savtsenko', 'Arnaud.Chevallier')


-- Security Group
SELECT *
FROM profiles.Profile AS P


-- Strategy and related book
SELECT 
	S.Id AS StrategyId,
	S.Caption AS StrageyName,
	S.Description AS StrategyDescription,
	B.Caption AS Book,
	B.Year AS YearBook,
	S.IsActive
FROM dbo.Strategy AS S
INNER JOIN dbo.Book AS B ON S.BookId = B.Id
ORDER BY B.Year DESC


-- Product Type
SELECT
	PT.Id AS ProductTypeId,
    PT.Caption AS ProductType,
    PT.Description 
FROM dbo.ProductType AS PT


-- Product Familly
SELECT 
	PF.Id AS ProductFamilyId,
	PF.Caption AS ProductFamily,
	PF.Description,
	PT.Caption AS ProductType
FROM dbo.ProductFamily AS PF
INNER JOIN ProductType AS PT ON PF.ProductTypeId = PT.Id

-- Product
SELECT 
	P.Id AS ProductId,
	P.ProductName,
	PF.Caption AS ProductFamily,
	PT.Caption AS ProductType,
	P.DegreeApi,
	p.Density,
	P.Mt2BblFactor,
	P.Density20,
	P.Basis
FROM dbo.Product AS P
INNER JOIN dbo.ProductFamily AS PF ON P.FamilyId = PF.Id
INNER JOIN dbo.ProductType AS PT ON PF.ProductTypeId = PT.Id


-- Product Polluant
SELECT 
	PP.Id AS PolluantId,
	PP.PollutantName,
	PP.EmissionFactor,
	P.ProductName
FROM dbo.ProductPollutant AS PP
INNER JOIN dbo.Product AS P ON PP.ProductId = P.Id

-- Continent
SELECT 
	C.Id AS ContinentId,
	C.ContinentName
FROM geo.Continent AS C

-- Country
SELECT 
	C.Id AS CountryId,
	C.CountryName,
	C.IsoCode2,
	C.IsoCode3,
	C2.ContinentName,
	C.BoundNorth,
	C.BoundEast,
	C.BoundSouth,
	C.BoundWest
FROM geo.Country AS C
INNER JOIN geo.Continent AS C2 ON C.ContinentId = C2.Id



-- Incoterm
SELECT 
	BD.Id AS IncotermId,
	BD.Caption AS IncotermAbbr,
	Bd.Description AS Incoterm
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'Incoterm'



-- Payment Term Type
SELECT 
	BD.Id AS PayTermSecurityTypeId,
	BD.Caption AS PayTermSecurityTypeAbbr,
	Bd.Description AS PayTermSecurityType
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'PayTermSecurityType'


SELECT BD.Caption, DPT.PayTermRangeSize, COUNT(*)
FROM dbo.DealPaymentTerm AS DPT
INNER JOIN dbo.Deal AS D ON DPT.DealId = D.Id
INNER JOIN dbo.BaseDefinition AS BD ON D.PayTermSecurityTypeId = BD.Id
WHERE 1=1
  AND DPT.PayTermRangeSize > 0
GROUP BY BD.Caption, DPT.PayTermRangeSize
ORDER BY 3 DESC




-- Delivery Period (no relation with time dimension)
SELECT 
	BD.Id AS DeliveryPeriodId,
	BD.Caption AS DeliveryPeriodAbbr,
	Bd.Description AS DeliveryPeriod
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'DeliveryPeriod'


-- Basis Document Type
SELECT 
	BD.Id AS BasisDocumentId,
	BD.Caption AS BasisDocumentAbbr,
	Bd.Description AS BasisDocument
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'BasisDocumentType'



-- BusinessArea
SELECT 
	BD.Id AS BusinessAreaId,
	BD.Caption AS BusinessAreaAbbr,
	Bd.Description AS BusinessArea
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'BusinessArea'




-- CostGroup
SELECT 
	BD.Id AS CostGroupId,
	BD.Caption AS CostGroupAbbr,
	Bd.Description AS CostGroup
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'CostGroup'

-- CostType
SELECT 
	--BD.Id AS CostTypeId,
	BD.Caption AS [code],
	Bd.Description AS [name],
	'SERVICES' AS [category],
	'Mt' AS [uom],
	0 AS [sale_price],
	0 AS [cost_price],
	'' AS [description]
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'CostType'
ORDER BY 1


-- Jurisdiction
SELECT 
	BD.Id AS JurisdictionId,
	BD.Caption AS JurisdictionAbbr,
	Bd.Description AS Jurisdiction
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'Jurisdiction'


-- Exchange
SELECT 
	BD.Id AS ExchangeId,
	BD.Caption AS ExchangeAbbr,
	Bd.Description AS Exchange
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'Exchange'


-- Quotation/Price Provider
SELECT 
	BD.Id AS SourceId,
	BD.Caption AS SourceAbbr,
	Bd.Description AS Source
FROM dbo.BaseDefinition AS BD
WHERE BD.ClassType = 'QuoteSource'

SELECT * FROM price.Symbol AS S
SELECT * FROM price.Maturity AS M
SELECT * FROM price.Quote AS Q

-- Terminal
SELECT 
	T.Id AS TerminalId,
	T.TerminalName,
	L.LocationName,
	C.Name AS TerminalOwner
FROM dbo.Terminal AS T
INNER JOIN geo.Location AS L ON T.LocationId = L.Id
INNER JOIN counterpart.Company AS C ON T.OwnerId = C.Id


-- Vessel
SELECT 
	T.Id AS VesselId,
	T.TransporterName,
	T.TransportType,
	T.Draught,
	T.BuildingYear,
	c.Name AS VesselOwner,
	C2.CountryName AS Flag,
	T.ImoNB,
	T.CharterType,
	T.DeadWeightMT
FROM dbo.Transporter AS T
LEFT JOIN counterpart.Company AS C ON T.OwnerId = C.Id
LEFT JOIN geo.Country AS C2 ON T.FlagId = C2.Id

-- Vessel for insert in TradOn (CSV file structure)
SELECT 
	(SELECT STRING_AGG(
		UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2, LEN(value))),
		' '
	) 
	FROM STRING_SPLIT(T.TransporterName, ' ')
	) AS vessel_name,
	T.BuildingYear		AS vessel_year,
	T.ImoNB				AS vessel_imo
FROM dbo.Transporter AS T







-- All EUCLID Locations
SELECT 
	L.Id AS LocationId,
	L.LocationName,
	L.CoordinateX,
	L.CoordinateY,
	C.CountryName,
	C2.ContinentName,
	BD.ClassType,
	BD.Caption
FROM geo.Location AS L
INNER JOIN geo.Country AS C ON L.CountryId = C.Id
INNER JOIN geo.Continent AS C2 ON C.ContinentId = C2.Id
INNER JOIN dbo.BaseDefinition AS BD ON L.TypeId = BD.Id


-- Stock Locations (supplier) - CSV file structure
  SELECT DISTINCT
	  LTRIM(RTRIM(LOA.LocationName))	AS [name],
	  'supplier'						AS [type],
	  LOA.CoordinateX					AS [lat],
	  LOA.CoordinateY					AS [lon]

  FROM		 dbo.Deal					AS D
  LEFT  JOIN geo.Location				AS LOA	ON D.LoadLocationId = LOA.Id
  LEFT  JOIN geo.Country				AS C_L  ON LOA.CountryId = C_L.Id
  --LEFT  JOIN geo.Location				AS DIS	ON D.DeliveryLocationId = DIS.Id
  --LEFT  JOIN geo.Country				AS C_D	ON DIS.CountryId	= C_D.Id
WHERE D.Status <> 'Cancelled'
  AND  ISNULL(LOA.LocationName, '') <> ''
  AND LOA.Id <> '8855ED73-344C-4F8D-B4D6-DD214D3B7E61'
ORDER BY 2

  
-- Stock locations (customer) - CSV file structure
SELECT DISTINCT
	 LTRIM(RTRIM(DIS.LocationName))							AS [name],
	  'customer'						AS [type],
	  DIS.CoordinateX					AS [lat],
	  DIS.CoordinateY					AS [lon]
  FROM		 dbo.Deal					AS D
  LEFT  JOIN geo.Location				AS DIS	ON D.DeliveryLocationId = DIS.Id
  LEFT  JOIN geo.Country				AS C_D	ON DIS.CountryId	= C_D.Id
  
WHERE D.Status <> 'Cancelled'
  AND  LTRIM(RTRIM(DIS.LocationName)) <> ''



-- Purchase contracts
;WITH PaymentTerm AS (
	SELECT D.Id AS DealId, BD.Caption, DPT.PayTermRangeSize
	FROM dbo.DealPaymentTerm AS DPT
	INNER JOIN dbo.Deal AS D ON DPT.DealId = D.Id
	LEFT JOIN dbo.BaseDefinition AS BD ON D.PayTermSecurityTypeId = BD.Id
	WHERE 1=1
	  AND DPT.PayTermRangeSize > 0
	GROUP BY D.Id , BD.Caption, DPT.PayTermRangeSize
),
Geo AS (
	SELECT 
		G.CountryName,
		G.ContinentName,
		G.BusinessArea
	FROM singa.VW_DIM_GEOGRAPHY AS G
	GROUP BY 
		G.CountryName,
		G.ContinentName,
		G.BusinessArea
),
PriceMtM AS
(
	SELECT 
		ERMtM.DealId,
		ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0) AS Maturity , 
		ISNULL(S.Description, '')	AS PriceBenchmark,
		M.MaturityYear				AS maturity_year,
		M.MaturityPeriod			AS maturity_month,
		M.MaturityDay				AS maturity_day
	   FROM dbo.ExpenseRevenue				AS ERMtM
	  INNER JOIN dbo.PriceFormulaElement	AS PFEMtM	ON PFEMtM.ExpenseRevenueId = ERMtM.Id AND PFEMtM.FormulaType <> 'Adjustment'
	  INNER JOIN price.Maturity				AS M	ON PFEMtM.MaturityId = M.Id
	  INNER JOIN price.Symbol				AS S	ON M.SymbolId = S.Id
	WHERE 1=1
	AND ERMtM.FormulaGroup = 2		
	AND PFEMtM.FormulaType LIKE 'MtM%'
	AND S.Description NOT LIKE 'Freight%'
	AND S.Description NOT LIKE 'Discount%'
	AND S.Description NOT LIKE 'BAF%'
),
Price AS
(
	SELECT 
		C.ContractNumber,
		C.CostDescription	AS pricing_curve,
		AVG(C.Price)		AS price
	FROM singa.VW_REP_PHYSICAL_CONTRACT_COSTS AS C
	WHERE 1=1
	  AND C.FormulaGroup = 1
	  AND C.CostDescription NOT IN ( 'Adjustment' )
	  AND C.CostDescription NOT LIKE ('%Discount%')
  
	GROUP BY
		C.ContractNumber,
		C.CostDescription
),
Pricing AS
(
	SELECT 
		D.Id			AS dealID,
		D.Reference		AS deal_number,
		MAX(
			IIF(
				S.Caption = 'Fixed Margin', 
				'Priced', 
				CASE WHEN ISNULL(PFE.FormulaType, '') <> 'Fixed' AND VMP.DealId IS NULL THEN 'Unpriced' 
				ELSE 'Priced' END
				)
			)	
						AS pricing,
		ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0) AS maturity,
		M.MaturityYear		AS maturity_year,
		M.MaturityPeriod	AS maturity_month,
		M.MaturityDay		AS maturity_day
	  FROM		 dbo.Deal					AS D
	  INNER JOIN dbo.ExpenseRevenue			AS ER	ON ER.DealId		= d.Id  AND ER.FormulaGroup NOT IN (2, 4)
	  LEFT  JOIN dbo.PriceFormulaElement	AS PFE	ON PFE.ExpenseRevenueId = ER.Id AND PFE.FormulaType <> 'Adjustment'
	  LEFT  JOIN singa.VW_MVT_PRICING		AS VMP  ON D.Id = VMP.DealId
	  INNER JOIN dbo.Strategy				AS S	ON D.StrategyId = S.Id	--All deals in Fixed Margin strategy must appear as Fixed

	  LEFT JOIN price.Maturity				AS M	ON PFE.MaturityId = M.Id
	  LEFT JOIN price.Symbol				AS SY	ON M.SymbolId = SY.Id
	GROUP BY
		D.Id,
		D.Reference,
		ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0),
		M.MaturityYear,
		M.MaturityPeriod,
		M.MaturityDay
)
SELECT  
	   D.Id													AS source_id
	 , D.Id													AS source_line_id
	 , D.Reference											AS number
	 , ISNULL(D.OtherReference, '')							AS reference
	 , ISNULL(D.ContractNumber, '')							AS our_reference
	 , C.name												AS party_name
	 , 'USD'												AS currency_code
	 , CAST(D.Date AS DATE)									AS purchase_date
	 
	 , CASE	
			WHEN PT.Caption = 'Open account' THEN CONCAT( 'NET ' , PT.PayTermRangeSize )
			WHEN PT.Caption = 'LC Doc' THEN CONCAT( 'LC ', PT.PayTermRangeSize, ' DAYS')
			ELSE CONCAT( 'NET ' , PT.PayTermRangeSize )
			END												AS payment_term
	 , ''													AS warehouse_code
	 , 'NCSW'												AS weight_basis	--'NCSW'

	 , ROUND(D.QuantityToleranceMinusPercent,2)				AS tol_min_pct
	 , ROUND(D.QuantityTolerancePlusPercent,2)				AS tol_max_pct
	 , D.QuantityToleranceMin								AS tol_min_qty
	 , D.QuantityToleranceMax								AS tol_max_qty
	 , ISNULL(LOA.LocationName, '')							AS from_location_name
	 , ISNULL(DIS.LocationName, '')							AS to_location_name
	 , ISNULL(INCO.Caption, '')								AS incoterm_name
	 , 'manual'												AS invoice_method
	 ,''													AS [description]
	 , CONCAT( D.Reference , ' / ' , B.Description , ' / ' , S.Caption)	AS [comment]
	 , 'line'												AS line_type
	 , CASE
		WHEN ISNULL(C2.CountryName,'') <> '' THEN CONCAT('H2SO4','-',C2.CountryName)
	 	ELSE 'H2SO4'
		END													AS line_product_code
	 , ISNULL(C2.CountryName,'')							AS origin
	 , ROUND(D.Quantity , 2)								AS line_quantity
	 , 'Mt'													AS line_unit_code
	 , ISNULL(ERV.Price ,0)									AS line_price
	 --, ISNULL(OLD_PRICE.AvgPrice,0)							AS line_old_price
	 , CONCAT(
		D.Quantity , ' Mt of sulphuric acid - ',
		CASE 
			WHEN ISNULL(D.CommodityBasis, 0) = 0 THEN 'Tel-quel' 
			ELSE CAST(D.CommodityBasis AS VARCHAR(MAX)) + '%'
			END)											AS line_description
	 , cast(D.DeliveryDateFrom AS DATE)						AS line_from_del
	 , cast(D.DeliveryDateTo AS DATE)						AS line_to_del
	 , 'bldate'												AS pricing_trigger
	 , CAST(D.EstimatedDateOfBL	AS DATE)					AS pricing_estimated_date
	 
	 , ISNULL( CONCAT( TRD.FirstName, ' ',  TRD.LastName) , '')		AS trader
	 , ISNULL( CONCAT( OP.FirstName, ' ', OP.LastName) , '')		AS operator

	 , ISNULL(D.CommodityBasis, 0)							AS concentration
	 , B.Description										AS book
	 , S.Caption											AS strategy
	 , ISNULL(BD.Caption , 'Laycan')						AS period_at
	 , ISNULL(D.Demurrage, '')								AS demurrage
	 , D.LaytimeHours										AS laytime_hours
	 , D.NoticeOfReadinessExtraHours						AS nor_extra_hours
	 , ISNULL(D.PumpingHourlyMTRate,0)						AS pumping_rate
	 , D.UseOnlyMinAndMax									AS use_only_min_max
	 , D.DropRemainingQuantity								AS drop_remaining_quantity
	 , C_CP.CountryName										AS counterparty_country
	 , GEO.BusinessArea										AS business_area
	 , ISNULL(PRC.pricing,'')								AS pricing_status
	 , ISNULL(ERV.pricing_curve, '')						AS price_curve
	 , CASE	
		WHEN PRC.maturity = 0 THEN NULL
		ELSE	
			CAST(CONCAT( PRC.maturity_year, '-', PRC.maturity_month, '-', ISNULL(PRC.maturity_day,'1')) AS DATE)
			END												AS price_maturity_date
	 , ISNULL(MtM.PriceBenchmark, '')						AS mtm_curve
	 , CASE	
		WHEN Mtm.maturity = 0 THEN NULL
		ELSE	
			CAST(CONCAT( Mtm.maturity_year, '-', Mtm.maturity_month, '-', ISNULL(Mtm.maturity_day,'1')) AS DATE)
			END												AS mtm_maturity_date
	 , CAST(D.EstimatedDateOfBL	AS DATE)					AS estimated_BL_date
  FROM		 dbo.Deal					AS D
  LEFT JOIN dbo.Book					AS B		ON D.BookId = B.Id
  LEFT JOIN dbo.ProductContainer		AS PC		ON D.Id = PC.Id
  LEFT  JOIN geo.Location				AS LOA		ON D.LoadLocationId = LOA.Id
  LEFT  JOIN geo.Country				AS C_L		ON LOA.CountryId = C_L.Id
  LEFT  JOIN geo.Location				AS DIS		ON D.DeliveryLocationId = DIS.Id
  LEFT  JOIN geo.Country				AS C_D		ON DIS.CountryId	= C_D.Id
  LEFT  JOIN counterpart.Company		AS C		ON D.CounterpartId	= C.Id
  LEFT  JOIN geo.Country				AS C_CP		ON C.CountryId		= C_CP.Id
  LEFT  JOIN Geo						AS GEO		ON C_CP.CountryName = GEO.CountryName
  LEFT  JOIN dbo.Product				AS P		ON D.ProductId		= P.Id
  LEFT  JOIN dbo.BaseDefinition			AS INCO		ON D.IncotermId = INCO.Id
  LEFT  JOIN dbo.Strategy				AS S		ON D.StrategyId = S.Id			
  LEFT  JOIN PaymentTerm				AS PT		ON D.Id = PT.DealId
  LEFT  JOIN (
	SELECT DealId, AVG(MvTPrice)  AS AvgPrice
	FROM [singa].[VW_MVT_PRICING]
	GROUP BY DealId)					AS OLD_PRICE ON D.Id = OLD_PRICE.DealId
  LEFT JOIN Price						AS ERV		ON D.Reference		= ERV.ContractNumber
  LEFT  JOIN Pricing					AS PRC		ON D.Id			= PRC.dealID
  LEFT  JOIN profiles.XUser				AS TRD		ON D.TraderId	= TRD.Id
  LEFT  JOIN profiles.XUser				AS OP		ON D.OperatorId = OP.Id
  LEFT  JOIN dbo.BaseDefinition			AS BD		ON D.DeliveryPeriodId = BD.Id
  LEFT  JOIN geo.Country				AS C2		ON D.CountryOfOriginId = C2.Id
  LEFT  JOIN PriceMtM					AS MTM		ON D.Id = MTM.DealId
WHERE 1=1 
  AND D.Status <> 'Cancelled'
  AND D.BuyOrSell = 1	-- Purchase contracts
  AND ISNULL(D.OtherReference, '') NOT LIKE '%ACCT Matching%'
  AND B.Description LIKE '%2025%'
ORDER BY 3,1
 


-- Sale contracts - Copy/Paste results of this query in CSV file
;WITH PaymentTerm AS (
	SELECT D.Id AS DealId, BD.Caption, DPT.PayTermRangeSize
	FROM dbo.DealPaymentTerm AS DPT
	INNER JOIN dbo.Deal AS D ON DPT.DealId = D.Id
	LEFT JOIN dbo.BaseDefinition AS BD ON D.PayTermSecurityTypeId = BD.Id
	WHERE 1=1
	  AND DPT.PayTermRangeSize > 0
	GROUP BY D.Id , BD.Caption, DPT.PayTermRangeSize
),
Geo AS (
	SELECT 
		G.CountryName,
		G.ContinentName,
		G.BusinessArea
	FROM singa.VW_DIM_GEOGRAPHY AS G
	GROUP BY 
		G.CountryName,
		G.ContinentName,
		G.BusinessArea
),
PriceMtM AS
(
	SELECT ERMtM.DealId,
		   ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0) AS maturity , 
		   ISNULL(S.Description, '')			AS PriceBenchmark,
		M.MaturityYear		AS maturity_year,
		M.MaturityPeriod	AS maturity_month,
		M.MaturityDay		AS maturity_day
	   FROM dbo.ExpenseRevenue				AS ERMtM
	  INNER JOIN dbo.PriceFormulaElement	AS PFEMtM	ON PFEMtM.ExpenseRevenueId = ERMtM.Id AND PFEMtM.FormulaType <> 'Adjustment'
	  INNER JOIN price.Maturity				AS M	ON PFEMtM.MaturityId = M.Id
	  INNER JOIN price.Symbol				AS S	ON M.SymbolId = S.Id
	WHERE 1=1
	AND ERMtM.FormulaGroup = 2		
	AND PFEMtM.FormulaType LIKE 'MtM%'
	AND S.Description NOT LIKE 'Freight%'
	AND S.Description NOT LIKE 'Discount%'
	AND S.Description NOT LIKE 'BAF%'
),
Price AS
(
	SELECT 
		C.ContractNumber,
		C.CostDescription	AS pricing_curve,
		AVG(C.Price)		AS price
	FROM singa.VW_REP_PHYSICAL_CONTRACT_COSTS AS C
	WHERE 1=1
	  AND C.FormulaGroup = 1
	  AND C.CostDescription NOT IN ( 'Adjustment' )
	  AND C.CostDescription NOT LIKE ('%Discount%')
  
	GROUP BY
		C.ContractNumber,
		C.CostDescription
),
Pricing AS
(
	SELECT 
		D.Id			AS dealID,
		D.Reference		AS deal_number,
		MAX(
			IIF(
				S.Caption = 'Fixed Margin', 
				'Priced', 
				CASE WHEN ISNULL(PFE.FormulaType, '') <> 'Fixed' AND VMP.DealId IS NULL THEN 'Unpriced' 
				ELSE 'Priced' END
				)
			)	
						AS pricing,
		ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0) AS maturity,
		M.MaturityYear		AS maturity_year,
		M.MaturityPeriod	AS maturity_month,
		M.MaturityDay		AS maturity_day
	  FROM		 dbo.Deal					AS D
	  INNER JOIN dbo.ExpenseRevenue			AS ER	ON ER.DealId		= d.Id  AND ER.FormulaGroup NOT IN (2, 4)
	  LEFT  JOIN dbo.PriceFormulaElement	AS PFE	ON PFE.ExpenseRevenueId = ER.Id AND PFE.FormulaType <> 'Adjustment'
	  LEFT  JOIN singa.VW_MVT_PRICING		AS VMP  ON D.Id = VMP.DealId
	  INNER JOIN dbo.Strategy				AS S	ON D.StrategyId = S.Id	--All deals in Fixed Margin strategy must appear as Fixed

	  LEFT JOIN price.Maturity				AS M	ON PFE.MaturityId = M.Id
	  LEFT JOIN price.Symbol				AS SY	ON M.SymbolId = SY.Id
	GROUP BY
		D.Id,
		D.Reference,
		ISNULL(M.MaturityYear * 10000 + M.MaturityPeriod * 100 + ISNULL(M.MaturityDay, 1), 0),
		M.MaturityYear,
		M.MaturityPeriod,
		M.MaturityDay
)
SELECT 
	   D.Id													AS source_id
	 , D.Id													AS source_line_id
	 , D.Reference											AS number
	 , ISNULL(D.OtherReference, '')							AS reference
	 , ISNULL(D.ContractNumber, '')							AS our_reference
	 , C.name												AS party_name
	 , 'USD'												AS currency_code
	 , CAST(D.Date AS DATE)									AS sale_date
	 
	 , CASE	
			WHEN PT.Caption = 'Open account' THEN CONCAT( 'NET ' , PT.PayTermRangeSize )
			WHEN PT.Caption = 'LC Doc' THEN CONCAT( 'LC ', PT.PayTermRangeSize, ' DAYS')
			ELSE CONCAT( 'NET ' , PT.PayTermRangeSize )
			END												AS payment_term

	 , ''													AS warehouse_code
	 , 'NCSW'												AS weight_basis	
	 , ROUND(D.QuantityToleranceMinusPercent,2)				AS tol_min_pct
	 , ROUND(D.QuantityTolerancePlusPercent,2)				AS tol_max_pct
	 , D.QuantityToleranceMin								AS tol_min_qty
	 , D.QuantityToleranceMax								AS tol_max_qty

	 , ISNULL(LOA.LocationName, '')							AS from_location_name
	 , ISNULL(DIS.LocationName, '')							AS to_location_name
	 , ISNULL(INCO.Caption, '')								AS incoterm_name
	 , 'manual'												AS invoice_method
	 ,''													AS [description]
	 , CONCAT( D.Reference , ' / ' , B.Description , ' / ' , S.Caption)	AS [comment]
	 , 'line'												AS line_type
	 , CASE
		WHEN ISNULL(C2.CountryName,'') <> '' THEN CONCAT('H2SO4','-',C2.CountryName)
	 	ELSE 'H2SO4'
		END													AS line_product_code
	 , ISNULL(C2.CountryName,'')							AS origin
	 , ROUND(D.Quantity , 2)								AS line_quantity
	 , 'Mt'													AS line_unit_code
	 , ISNULL(ERV.Price ,0)									AS line_price
	 --, ISNULL(OLD_PRICE.AvgPrice,0)							AS line_old_price
	 , CONCAT(
		D.Quantity , ' Mt of sulphuric acid - ',
		CASE 
			WHEN ISNULL(D.CommodityBasis, 0) = 0 THEN 'Tel-quel' 
			ELSE CAST(D.CommodityBasis AS VARCHAR(MAX)) + '%'
			END)											AS line_description
	 , cast(D.DeliveryDateFrom AS DATE)						AS line_from_del
	 , cast(D.DeliveryDateTo AS DATE)						AS line_to_del
	 , 'bldate'												AS pricing_trigger
	 , CAST(D.EstimatedDateOfBL	AS DATE)					AS pricing_estimated_date

	 , ISNULL( CONCAT( TRD.FirstName, ' ',  TRD.LastName) , '')		AS trader
	 , ISNULL( CONCAT( OP.FirstName, ' ', OP.LastName) , '')		AS operator

	 , ISNULL(D.CommodityBasis, 0)							AS concentration
	 , B.Description										AS book
	 , S.Caption											AS strategy
	 , ISNULL(BD.Caption , 'Laycan')						AS period_at
	 , ISNULL(D.Demurrage, '')								AS demurrage
	 , D.LaytimeHours										AS laytime_hours
	 , D.NoticeOfReadinessExtraHours						AS nor_extra_hours
	 , ISNULL(D.PumpingHourlyMTRate,0)						AS pumping_rate
	 , D.UseOnlyMinAndMax									AS use_only_min_max
	 , D.DropRemainingQuantity								AS drop_remaining_quantity
	 , C_CP.CountryName										AS counterparty_country
	 , GEO.BusinessArea										AS business_area
	 , ISNULL(PRC.pricing,'')								AS pricing_status
	 , ISNULL(ERV.pricing_curve, '')						AS price_curve
	 , CASE	
		WHEN PRC.maturity = 0 THEN NULL
		ELSE	
			CAST(CONCAT( PRC.maturity_year, '-', PRC.maturity_month, '-', ISNULL(PRC.maturity_day,'1')) AS DATE)
			END												AS price_maturity_date
	 , ISNULL(MtM.PriceBenchmark, '')						AS mtm_curve
	 , CASE	
		WHEN Mtm.maturity = 0 THEN NULL
		ELSE	
			CAST(CONCAT( Mtm.maturity_year, '-', Mtm.maturity_month, '-', ISNULL(Mtm.maturity_day,'1')) AS DATE)
			END												AS mtm_maturity_date
	 , CAST(D.EstimatedDateOfBL	AS DATE)					AS estimated_BL_date
  FROM		 dbo.Deal					AS D
  LEFT JOIN dbo.Book					AS B		ON D.BookId = B.Id
  LEFT JOIN dbo.ProductContainer		AS PC		ON D.Id = PC.Id
  LEFT  JOIN geo.Location				AS LOA		ON D.LoadLocationId = LOA.Id
  LEFT  JOIN geo.Country				AS C_L		ON LOA.CountryId = C_L.Id
  LEFT  JOIN geo.Location				AS DIS		ON D.DeliveryLocationId = DIS.Id
  LEFT  JOIN geo.Country				AS C_D		ON DIS.CountryId	= C_D.Id
  LEFT  JOIN counterpart.Company		AS C		ON D.CounterpartId	= C.Id
  LEFT  JOIN geo.Country				AS C_CP		ON C.CountryId		= C_CP.Id
  LEFT  JOIN Geo						AS GEO		ON C_CP.CountryName = GEO.CountryName
  LEFT  JOIN dbo.Product				AS P		ON D.ProductId		= P.Id
  LEFT  JOIN dbo.BaseDefinition			AS INCO		ON D.IncotermId = INCO.Id
  LEFT  JOIN dbo.Strategy				AS S		ON D.StrategyId = S.Id			
  LEFT JOIN PaymentTerm					AS PT		ON D.Id = PT.DealId
  LEFT JOIN (
	SELECT DealId, AVG(MvTPrice)  AS AvgPrice
	FROM [singa].[VW_MVT_PRICING]
	GROUP BY DealId)					AS OLD_PRICE	ON D.Id = OLD_PRICE.DealId
  LEFT  JOIN Price						AS ERV		ON D.Reference		= ERV.ContractNumber
  LEFT  JOIN Pricing					AS PRC		ON D.Id			= PRC.dealID
  LEFT  JOIN profiles.XUser				AS TRD		ON D.TraderId	= TRD.Id
  LEFT  JOIN profiles.XUser				AS OP		ON D.OperatorId = OP.Id
  LEFT  JOIN dbo.BaseDefinition			AS BD		ON D.DeliveryPeriodId = BD.Id
  LEFT  JOIN geo.Country				AS C2		ON D.CountryOfOriginId = C2.Id
  LEFT  JOIN PriceMtM					AS MTM		ON D.Id = MTM.DealId
WHERE 1=1 
  AND D.Status <> 'Cancelled'
  AND D.BuyOrSell = -1	-- Sale contracts
  AND ISNULL(D.OtherReference, '') NOT LIKE '%ACCT Matching%'
  AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
  --AND D.Reference = 2112
ORDER BY 3,1



-- Purchase contract costs
SELECT  

	D.Reference											AS contract_number,
	--B.Description,
	--CASE D.BuyOrSell
	--	WHEN '1' THEN 'Purchase'
	--	ELSE 'Sale'
	--	END												AS contract_type,
	LTRIM(RTRIM( ISNULL(D.OtherReference, '') ))		AS contract_ref,
	1													AS line_sequence,
	CASE	
		WHEN CT.Caption	= 'Commision'	THEN 'Commission'
		WHEN CT.Caption	= 'Freight'	THEN 'Maritime Freight'
		ELSE CT.Caption
		END												AS product,
	LTRIM(RTRIM( ISNULL(C.Name	, 'TBD Supplier') ))	AS supplier,
	ER.CurrencyCode										AS currency,
	CASE ER.IsRevenue
		WHEN 1 THEN 'REC'
		ELSE 'PAY'
	END													AS p_r,
	'Per qt'											AS mode,
	ROUND ( 
		(IIF(PFE.Quantity IS NULL, PFE.WeightBalance / 100, 1) * PFE.OutputPriceValue),			
		2 )												AS price,
	ER.UnitReference									AS unit
										
FROM dbo.Deal							AS D
LEFT JOIN dbo.Book						AS B	ON D.BookId = B.Id
INNER JOIN dbo.ExpenseRevenue			AS ER	ON D.Id = ER.DealId 
INNER JOIN dbo.PriceFormulaElement		AS PFE	ON PFE.ExpenseRevenueId = ER.Id
INNER JOIN dbo.BaseDefinition			AS CT	ON ER.CostTypeId = CT.Id
LEFT  JOIN counterpart.Company			AS C	ON ER.CounterpartId = c.Id
WHERE 1=1 
  AND D.Status						<> 'Cancelled'
  AND D.BuyOrSell					= 1	-- Purchase contracts
  AND ISNULL(D.OtherReference, '')	NOT LIKE '%ACCT Matching%'
  --AND (B.Description LIKE '%2025%')
  AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
  AND ER.FormulaGroup NOT IN (1,2) -- Not Price or MtM
ORDER BY 1




-- Sale contract costs
SELECT  

	D.Reference											AS contract_number,
	--B.Description,
	--CASE D.BuyOrSell
	--	WHEN '1' THEN 'Purchase'
	--	ELSE 'Sale'
	--	END												AS contract_type,
	LTRIM(RTRIM( ISNULL(D.OtherReference, '') ))		AS contract_ref,
	1													AS line_sequence,
	CASE	
		WHEN CT.Caption	= 'Commision'	THEN 'Commission'
		WHEN CT.Caption	= 'Freight'	THEN 'Maritime Freight'
		ELSE CT.Caption
		END												AS product,
	LTRIM(RTRIM( ISNULL(C.Name	, 'TBD Supplier') ))	AS supplier,
	ER.CurrencyCode										AS currency,
	CASE ER.IsRevenue
		WHEN 1 THEN 'REC'
		ELSE 'PAY'
	END													AS p_r,
	'Per qt'											AS mode,
	ROUND ( 
		(IIF(PFE.Quantity IS NULL, PFE.WeightBalance / 100, 1) * PFE.OutputPriceValue),			
		2 )												AS price,
	ER.UnitReference									AS unit
										
FROM dbo.Deal							AS D
LEFT JOIN dbo.Book						AS B	ON D.BookId = B.Id
INNER JOIN dbo.ExpenseRevenue			AS ER	ON D.Id = ER.DealId 
INNER JOIN dbo.PriceFormulaElement		AS PFE	ON PFE.ExpenseRevenueId = ER.Id
INNER JOIN dbo.BaseDefinition			AS CT	ON ER.CostTypeId = CT.Id
LEFT  JOIN counterpart.Company			AS C	ON ER.CounterpartId = c.Id
WHERE 1=1 
  AND D.Status						<> 'Cancelled'
  AND D.BuyOrSell					<> 1	-- Sale contracts
  AND ISNULL(D.OtherReference, '')	NOT LIKE '%ACCT Matching%'
  AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
  AND ER.FormulaGroup NOT IN (1,2) -- Not Price or MtM
ORDER BY 1




-- Run that script to generate CSV for price_curve MTM used in physical contract in 2025 and 2026
SELECT DISTINCT
	CONCAT(
		ISNULL(S.Description, '') ,  ' ' ,
		M.MaturityYear , '-',
		RIGHT(CONCAT('0', M.MaturityPeriod),2)
	)AS price_index,
	Q.QuoteDate	AS price_date,
	Q.Value		AS high_price,
	Q.Value		AS low_price,
	Q.Value		AS open_price,
	Q.Value		AS price_value
FROM dbo.ExpenseRevenue				AS ERMtM
INNER JOIN dbo.Deal					AS D		ON ERMtM.DealId = D.Id
INNER JOIN dbo.PriceFormulaElement	AS PFEMtM	ON PFEMtM.ExpenseRevenueId = ERMtM.Id AND PFEMtM.FormulaType <> 'Adjustment'
INNER JOIN price.Maturity			AS M		ON PFEMtM.MaturityId = M.Id
INNER JOIN price.Symbol				AS S		ON M.SymbolId = S.Id
INNER JOIN dbo.Book					AS B		ON D.BookId = B.Id
LEFT  JOIN price.Quote				AS Q		ON M.Id = Q.MaturityId 
WHERE 1=1
AND ERMtM.FormulaGroup = 2		
AND PFEMtM.FormulaType LIKE 'MtM%'
AND S.Description NOT LIKE 'Freight%'
AND S.Description NOT LIKE 'Discount%'
AND S.Description NOT LIKE 'BAF%'
AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
ORDER BY 1,2


SELECT DISTINCT formulaType FROM PriceFormulaElement

Trigger, RangeDate




-- Run that script to generate CSV for price_curve MTM used in physical contract in 2025 and 2026
SELECT DISTINCT
	D.Reference,
	PFEMtM.FormulaType,
	CONCAT(
		ISNULL(S.Description, '') ,  ' ' ,
		M.MaturityYear , '-',
		RIGHT(CONCAT('0', M.MaturityPeriod),2)
	)AS price_index,
	--Q.QuoteDate	AS price_date,
	--Q.Value		AS high_price,
	--Q.Value		AS low_price,
	--Q.Value		AS open_price,
	--Q.Value		AS price_value,

	PFEMtM.FirstDate,
	PFEMtM.LastDate,
	PFEMtM.TriggerDate,
	PFEMtM.NbDaysBefore,
	PFEMtM.NbDaysAfter,

	PFEMtM.*
FROM dbo.ExpenseRevenue				AS ERMtM
INNER JOIN dbo.Deal					AS D		ON ERMtM.DealId = D.Id
INNER JOIN dbo.PriceFormulaElement	AS PFEMtM	ON PFEMtM.ExpenseRevenueId = ERMtM.Id AND PFEMtM.FormulaType <> 'Adjustment'
INNER JOIN price.Maturity			AS M		ON PFEMtM.MaturityId = M.Id
INNER JOIN price.Symbol				AS S		ON M.SymbolId = S.Id
INNER JOIN dbo.Book					AS B		ON D.BookId = B.Id
LEFT  JOIN price.Quote				AS Q		ON M.Id = Q.MaturityId 
WHERE 1=1
AND ERMtM.FormulaGroup = 1		
AND PFEMtM.FormulaType NOT LIKE 'MtM%'
AND S.Description NOT LIKE 'Freight%'
AND S.Description NOT LIKE 'Discount%'
AND S.Description NOT LIKE 'BAF%'
AND (B.Description LIKE '%2025%' OR B.Description LIKE '%2026%')
--AND D.Reference = 2097
ORDER BY 1,2