Notes

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--EXEC stpRaporlarPartiFisi @PartiID = 1, @MakinaRef = 'KM2'
ALTER PROC [dbo].[stpRaporlarPartiFisi]
(
@PartiID INT,
@MakinaRef nvarchar(50)
)
AS

BEGIN

--Master
declare @topsayi int
select @topsayi = count(ID) from tblTop where PartiRef = @PartiID and Durum =1
SELECT DISTINCT
tblParti.ID, tblParti.SistemPartiNo AS [Sistem Parti No], tblParti.KumasKodu AS [Kumaş Kodu], tblParti.KumasCinsi AS [Kumaş Cinsi],
tblMusteri.MusteriAdi AS [Müşteri Adı], tblParti.SiparisNo AS [Sipariş No], tblParti.Aciklama AS Açıklama, CONVERT(numeric(18, 2), tblParti.En) AS En,
CONVERT(numeric(18, 2), tblParti.Gramaj) AS Gramaj, CONVERT(numeric(18, 2), tblParti.GirisKg) AS [Giriş Kg], CONVERT(numeric(18, 2), tblParti.GirisMt) AS [Giriş Mt],
tblParti.RenkNo AS [Renk No], tblParti.SiparisPartiNo AS [Sipariş Parti No], CONVERT(numeric(18, 2), tblParti.PartiKg) AS [Parti Kg], CONVERT(numeric(18,
2), tblParti.PartiMt) AS [Parti Mt], tblParti.PartiTop AS [Parti Top], tblParti.CusRef AS [Cus Ref], tblParti.CusOrderNo AS [Cus Order No], tblKaliteSistem.Sistem,
tblParti.Musteri AS Müşteri, tblParti.Ek0 as [Ham Kumaş En],
tblParti.Ek1 as [Ham Kumaş Gramaj], tblParti.Ek2 as [Çekmezlik En], tblParti.Ek3 as [Çekmezlik Boy],
tblParti.Ek4 as [Yık. Sonu En], tblParti.Ek5 as [Yık. Sonu Gramaj]
FROM tblParti INNER JOIN
tblMusteri ON tblParti.Musteri = tblMusteri.MusteriKodu INNER JOIN
tblKaliteSistem ON tblParti.KaliteSistemiRef = tblKaliteSistem.ID INNER JOIN
tblTop ON tblParti.ID = tblTop.PartiRef
WHERE (tblParti.ID = @PartiID) AND (tblParti.Durum = 1) AND (tblTop.Durum = 1)

print '1'


--Detay
SELECT tblTop.ID AS TopID, tblTop.TopNo AS [Top No], (CASE tblTop.Gonder WHEN 1 THEN 'OK' ELSE '' END) AS [Aktarım Sonuç],
tblTop.KontrolTarihi AS [Kontrol Tarihi], CONVERT(numeric(18, 2), tblTop.Metre) AS Metre, CONVERT(numeric(18, 2), tblTop.Kg) AS Kg,
CONVERT(numeric(18, 2), tblTop.En) AS En, tblMakina.Makina, tblTop.ParcaMetre, CONVERT(numeric(18, 2), tblParti.Gramaj) AS Gramaj,
CONVERT(numeric(18, 2), tblTop.KalitePuani) AS [Kalite Puanı], COUNT(tblTopHata.ID) AS [Hata Adedi], tblTop.Kalite, tblTop.Ek0 AS makina,
tblTop.Aciklama AS engramaj, tblTop.Ek2 AS postabasi, tblTop.Ek3 AS vardiya, tblTop.Ek4, tblKullanici.AdSoyad, 0 AS TeorikKg, tblTop.TeorikMt
FROM tblTop INNER JOIN
tblParti ON tblTop.PartiRef = tblParti.ID LEFT OUTER JOIN
tblKullanici ON tblTop.KontrolEden = tblKullanici.ID LEFT OUTER JOIN
tblTopHata ON tblTop.ID = tblTopHata.TopRef LEFT OUTER JOIN
tblMakina ON tblTop.MakinaRef = tblMakina.MakinaKodu
GROUP BY tblTop.TopNo, tblTop.Gonder, tblTop.KontrolTarihi, tblTop.Kg, tblTop.Metre, tblTop.En, tblTop.KontrolEden, tblMakina.Makina, tblParti.Gramaj,
tblTop.KalitePuani, tblTop.Kalite, tblParti.ID, tblParti.Durum, tblTop.ID, tblTop.Durum, tblTop.ParcaMetre, tblTop.Ek0, tblTop.Aciklama, tblTop.Ek2, tblTop.Ek3,
tblTop.Ek4, tblKullanici.AdSoyad, tblTop.TeorikKg, tblParti.Ek6, tblTop.TeorikMt
HAVING (tblParti.ID = @PartiID) AND (tblParti.Durum = 1) AND (tblTop.Durum = 1)

print '2'
END


--sakat kumaş kilosu
SELECT SUM(tblTop.Kg) AS SakatKg
FROM tblParti INNER JOIN
tblTop ON tblParti.ID = tblTop.PartiRef INNER JOIN
tblKaliteAralik ON tblParti.ID = tblKaliteAralik.ID AND tblTop.Kalite = tblKaliteAralik.Kalite
where (tblKaliteAralik.ID = 4) and
(tblParti.ID = @PartiID) AND (tblParti.Durum = 1) AND
(tblTop.Durum = 1)
GROUP BY tblKaliteAralik.ID