-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2026, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
--

-------------------------------------------------------------------------------------------------- 
-- alle Artikelposten aus Rechnungsbelegen ohne Alternativ, Bedarf und Entfaellt
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V'   and [name] = 'itcview_ArtikelVerkauf')
  DROP VIEW [dbo].[itcview_ArtikelVerkauf]
GO
CREATE VIEW dbo.itcview_ArtikelVerkauf
AS
  select 
    bp.RecId, be.JAHR, be.MONAT, be.TAG, bp.PrjNr, bp.BelArt, bp.BelNr, bp.RefKey, 
    bp.SatzArt, bp.PosNr, bp.ArtNr, bp.Ktxt1, 
	bp.Menge, bp.AbsMenge, bp.Me, bp.Aufwand, RabattVH,
	bp.MatJN,  EkMat, EkNe, Gebinde, Pe, pe.Faktor as PeFaktor, VkMat, 
	bp.LohnJN, EkLohn, Zeit, VkLohn,
	((bp.EkMat + bp.EkNe) / (bp.Gebinde * pe.Faktor)) * bp.AbsMenge * bp.Aufwand AS TotalEkMat,
	bp.VkMat * bp.AbsMenge * bp.Aufwand AS TotalVkMat,
	bp.EkLohn * bp.AbsMenge * bp.Aufwand AS TotalEkLohn,
	bp.VkLohn * bp.AbsMenge * bp.Aufwand AS TotalVkLohn
  from ut_BelPos bp
  inner join ut_Belegarten ba on ba.Nr = bp.BelArt and ba.Rechnung = 1
  inner join ut_Preiseinheiten pe on pe.Nr = bp.Pe
  inner join ut_Belege be on be.PRJNR = bp.PrjNr AND be.BELEGART = bp.BelArt AND be.BELEGNR = bp.BelNr
  where AbsMenge <> 0 and ArtNr <> '' and (SatzArt = 7 or SatzArt = 8) 
    and Alternativ = 0 and Bedarf = 0 and Entfaellt = 0
GO

select * from dbo.itcview_ArtikelVerkauf where ArtNr = 'GCEV' and JAHR = '2025'

select ArtNr, SUM(AbsMenge) AS Menge, SUM(Zeit / 60) AS Stunden, SUM(TotalVkLohn) AS Lohn, SUM(TotalVkMat) AS Material
from dbo.itcview_ArtikelVerkauf 
where JAHR = '2025' AND ArtNr in ('GCEV', 'GCEV10')
group by ArtNr 

select ArtNr, JAHR, MONAT, SUM(AbsMenge) AS Menge, SUM(ROUND(Zeit / 60,2)) AS Stunden, SUM(TotalVkLohn) AS Lohn, SUM(TotalVkMat) AS Material
from dbo.itcview_ArtikelVerkauf 
where JAHR = '2025' AND ArtNr in ('GCEV', 'GCEV10')
group by ArtNr, JAHR, MONAT 

