SentezVOGUE Konfeksiyon Yönetim Sistemi

SentezVOGUE Konfeksiyon Yönetim Sistemi SQL Sorguları

• Günlük Kesim Durumu
  select K.ORDERNO [Order No],K.MODEL [Model No],M.ADI [Model Adı],OD.R1[ Varyant],R1.ADI [Varyant Adı],SUM(K.B1+K.B2+K.B3+K.B4+K.B5+K.B6+K.B7+K.B8+K.B9+K.B10) [K.A.] from KESIM K
  left join MODEL M on M.KOD=K.MODEL
  left join ORDET OD on OD.ORDERNO=K.ORDERNO and OD.SATIR=K.SATIR
  left join RENK R1 on R1.TIP=0 and R1.KOD=OD.R1
  group by K.ORDERNO,K.MODEL,K.SATIR,K.TARIH,OD.R1,R1.ADI,M.ADI
  having floor(cast(K.TARIH as float))=floor(cast(GETDATE() AS float))
  order by K.ORDERNO,K.MODEL
• Günlük Dikim Durumu (dikim parça işçilik numarası 5 olarak kabul edilmiştir)
  select D.ORDERNO [Order No],D.MODEL [Model No],M.ADI [Model Adı],MUS.ADI as [Atölye],OD.R1[ Varyant],R1.ADI [Varyant Adı]
  ,CASE WHEN D.GC=1 THEN 'Giden' ELSE 'Gelen' END as [GÇ],SUM(D.B1+D.B2+D.B3+D.B4+D.B5+D.B6+D.B7+D.B8+D.B9+D.B10) as [DA]
  from DIKIM D
  left join MODEL M on M.KOD=D.MODEL
  left join ORDET OD on OD.ORDERNO=D.ORDERNO and OD.SATIR=D.SATIR
  left join RENK R1 on R1.TIP=0 and R1.KOD=OD.R1
  left join MUSTERI MUS ON MUS.SIRKET='01' and MUS.KOD=D.ATOLYE
  group by D.ORDERNO,D.MODEL,D.SATIR,D.ATOLYE,D.TIP,D.TARIH,D.GC,OD.R1,R1.ADI,MUS.ADI,D.PSATIR,M.ADI
  having D.TIP=5 AND D.PSATIR=1 AND floor(cast(D.TARIH as float))=floor(cast(GETDATE() AS float))
  order by D.ORDERNO,D.MODEL,D.ATOLYE,D.SATIR,D.GC
• Günlük Kalite Kontrol Durumu (kalite kontrol parça işçilik numarası 7 olarak kabul edilmiştir)
  select D.ORDERNO [Order No],D.MODEL [Model No],M.ADI [Model Adı],MUS.ADI as [Atölye],OD.R1[ Varyant],R1.ADI [Varyant Adı]
  ,case when D.GC=1 then 'Giden' else 'Gelen' end as [GÇ],sum(D.B1+D.B2+D.B3+D.B4+D.B5+D.B6+D.B7+D.B8+D.B9+D.B10) as [DA]
  from DIKIM D
  left join MODEL M on M.KOD=D.MODEL
  left join ORDET OD on OD.ORDERNO=D.ORDERNO and OD.SATIR=D.SATIR
  left join RENK R1 on R1.TIP=0 and R1.KOD=OD.R1
  left join MUSTERI MUS ON MUS.SIRKET='01' and MUS.KOD=D.ATOLYE
  GROUP BY D.ORDERNO,D.MODEL,D.SATIR,D.ATOLYE,D.TIP,D.TARIH,D.GC,OD.R1,R1.ADI,MUS.ADI,D.PSATIR,M.ADI
  having D.TIP=7 AND D.PSATIR=1 AND FLOOR(CAST(D.TARIH as float))=FLOOR(CAST(GETDATE() AS FLOAT))
  ORDER BY D.ORDERNO,D.MODEL,D.ATOLYE,D.SATIR,D.GC
• Günlük Sevkiyat Durumu
  select CB.ORDERNO [Order No],CB.MODEL [Model No],M.ADI [Model Adı],OD.R1[ Varyant],R1.ADI [Varyant Adı]
  ,sum((CB.B1+CB.B2+CB.B3+CB.B4+CB.B5+CB.B6+CB.B7+CB.B8+CB.B9+CB.B10+CB.B11+CB.B12+
  CB.B13+CB.B14+CB.B15+CB.B16+CB.B17+CB.B18+CB.B19+CB.B20+CB.B21+CB.B22+CB.B23+
  CB.B24+CB.B25+CB.B26+CB.B27+CB.B28+CB.B29+CB.B30)* isnull(case when CB.KOLI2 <> CB.KOLI then
  (CB.KOLI2-CB.KOLI +1) else 1 end,1)) [Sevk Adet] from CEKIB CB
  left join ORDET OD on OD.ORDERNO=CB.ORDERNO and OD.SATIR=CB.SATIR
  left join RENK R1 on R1.TIP=0 and R1.KOD=OD.R1
  left join MODEL M on M.KOD=CB.MODEL
  GROUP BY CB.ORDERNO,CB.MODEL,CB.RKOD,CB.TARIH,OD.R1,R1.ADI,M.ADI
  having FLOOR(CAST(CB.TARIH as float))=FLOOR(CAST(GETDATE() AS FLOAT))
  ORDER BY CB.ORDERNO,CB.MODEL
• Günlük / Kümülatif Üretim Durumu (tasnif parça işçilik numarası 1 , dikim parça işçilik numarası 5 olarak kabul edilmiştir)
  Select O.ORDERNO As [Order No], O.MODEL1 As [Model No], M.ADI As [Model Adı]
  ,(Select Sum(K.B1 + K.B2 + K.B3 + K.B4 + K.B5 + K.B6 + K.B7 + K.B8 + K.B9 + K.B10 + K.B11 + K.B12 + K.B13 + K.B14 + K.B15 + K.B16 + K.B17 + K.B18 + K.B19 + K.B20 + K.B21 + K.B22 + K.B23 + K.B24 + K.B25 + K.B26 + K.B27 + K.B28 + K.B29 + K.B30) From KESIM K Where K.ORDERNO = O.ORDERNO And K.MODEL = O.MODEL1 And K.PSATIR = 1 And K.TARIH = FLOOR(Cast(GETDATE() As Float))) [KA (Bugün)]
  ,(Select Sum(K.B1 + K.B2 + K.B3 + K.B4 + K.B5 + K.B6 + K.B7 + K.B8 + K.B9 + K.B10 + K.B11 + K.B12 + K.B13 + K.B14 + K.B15 + K.B16 + K.B17 + K.B18 + K.B19 + K.B20 + K.B21 + K.B22 + K.B23 + K.B24 + K.B25 + K.B26 + K.B27 + K.B28 + K.B29 + K.B30) From KESIM K Where K.ORDERNO = O.ORDERNO And K.MODEL = O.MODEL1 And K.PSATIR = 1) [KA (Kümülatif)]
  ,(Select Sum(D.B1 + D.B2 + D.B3 + D.B4 + D.B5 + D.B6 + D.B7 + D.B8 + D.B9 + D.B10 + D.B11 + D.B12 + D.B13 + D.B14 + D.B15 + D.B16 + D.B17 + D.B18 + D.B19 + D.B20 + D.B21 + D.B22 + D.B23 + D.B24 + D.B25 + D.B26 + D.B27 + D.B28 + D.B29 + D.B30) From DIKIM D Where D.TIP = 1 And D.GC = 2 And D.PSATIR = 1 And D.ORDERNO = O.ORDERNO And D.MODEL = O.MODEL1 And D.TARIH = FLOOR(Cast(GETDATE() As Float))) [Tasnif(Bugün)]
  ,(Select Sum(D.B1 + D.B2 + D.B3 + D.B4 + D.B5 + D.B6 + D.B7 + D.B8 + D.B9 + D.B10 + D.B11 + D.B12 + D.B13 + D.B14 + D.B15 + D.B16 + D.B17 + D.B18 + D.B19 + D.B20 + D.B21 + D.B22 + D.B23 + D.B24 + D.B25 + D.B26 + D.B27 + D.B28 + D.B29 + D.B30) From DIKIM D Where D.TIP = 1 And D.GC = 2 And D.PSATIR = 1 And D.ORDERNO = O.ORDERNO And D.MODEL = O.MODEL1) [Tasnif(Kümülatif)]
  ,(Select Sum(D.B1 + D.B2 + D.B3 + D.B4 + D.B5 + D.B6 + D.B7 + D.B8 + D.B9 + D.B10 + D.B11 + D.B12 + D.B13 + D.B14 + D.B15 + D.B16 + D.B17 + D.B18 + D.B19 + D.B20 + D.B21 + D.B22 + D.B23 + D.B24 + D.B25 + D.B26 + D.B27 + D.B28 + D.B29 + D.B30) From DIKIM D Where D.TIP = 5 And D.GC = 2 And D.PSATIR = 1 And D.ORDERNO = O.ORDERNO And D.MODEL = O.MODEL1 And D.TARIH = FLOOR(Cast(GETDATE() As Float))) [Dikim(Bugün)]
  ,(Select Sum(D.B1 + D.B2 + D.B3 + D.B4 + D.B5 + D.B6 + D.B7 + D.B8 + D.B9 + D.B10 + D.B11 + D.B12 + D.B13 + D.B14 + D.B15 + D.B16 + D.B17 + D.B18 + D.B19 + D.B20 + D.B21 + D.B22 + D.B23 + D.B24 + D.B25 + D.B26 + D.B27 + D.B28 + D.B29 + D.B30) From DIKIM D Where D.TIP = 5 And D.GC = 2 And D.PSATIR = 1 And D.ORDERNO = O.ORDERNO And D.MODEL = O.MODEL1) [Dikim(Kümülatif)]
  ,(Select Sum((CB.B1 + CB.B2 + CB.B3 + CB.B4 + CB.B5 + CB.B6 + CB.B7 + CB.B8 + CB.B9 + CB.B10 + CB.B11 + CB.B12 + CB.B13 + CB.B14 + CB.B15 + CB.B16 + CB.B17 + CB.B18 + CB.B19 + CB.B20 + CB.B21 + CB.B22 + CB.B23 + CB.B24 + CB.B25 + CB.B26 + CB.B27 + CB.B28 + CB.B29 + CB.B30) * isnull(Case When CB.KOLI2 <> CB.KOLI Then (CB.KOLI2 - CB.KOLI + 1) Else 1 End, 1)) From CEKIB CB Where CB.ORDERNO = O.ORDERNO And CB.MODEL = O.MODEL1 And CB.TARIH = FLOOR(Cast(GETDATE() As Float))) [Sevk(Bugün)]
  ,(Select Sum((CB.B1 + CB.B2 + CB.B3 + CB.B4 + CB.B5 + CB.B6 + CB.B7 + CB.B8 + CB.B9 + CB.B10 + CB.B11 + CB.B12 + CB.B13 + CB.B14 + CB.B15 + CB.B16 + CB.B17 + CB.B18 + CB.B19 + CB.B20 + CB.B21 + CB.B22 + CB.B23 + CB.B24 + CB.B25 + CB.B26 + CB.B27 + CB.B28 + CB.B29 + CB.B30) * isnull(Case When CB.KOLI2 <> CB.KOLI Then (CB.KOLI2 - CB.KOLI + 1) Else 1 End, 1)) From CEKIB CB Where CB.ORDERNO = O.ORDERNO And CB.MODEL = O.MODEL1) [Sevk(Kümülatif)]
  From ORDERH O
  Left Outer Join MODEL M On M.KOD = O.MODEL1
  Where isnull((Select Sum(K.B1 + K.B2 + K.B3 + K.B4 + K.B5 + K.B6 + K.B7 + K.B8 + K.B9 + K.B10 + K.B11 + K.B12 + K.B13 + K.B14 + K.B15 + K.B16 + K.B17 + K.B18 + K.B19 + K.B20 + K.B21 + K.B22 + K.B23 + K.B24 + K.B25 + K.B26 + K.B27 + K.B28 + K.B29 + K.B30) From KESIM K Where K.ORDERNO = O.ORDERNO And K.MODEL = O.MODEL1 And K.PSATIR = 1 And K.TARIH = FLOOR(Cast(GETDATE() As Float))), 0.0) <> 0 Or isnull((Select Sum(D.B1 + D.B2 + D.B3 + D.B4 + D.B5 + D.B6 + D.B7 + D.B8 + D.B9 + D.B10 + D.B11 + D.B12 + D.B13 + D.B14 + D.B15 + D.B16 + D.B17 + D.B18 + D.B19 + D.B20 + D.B21 + D.B22 + D.B23 + D.B24 + D.B25 + D.B26 + D.B27 + D.B28 + D.B29 + D.B30) From DIKIM D Where D.TIP = 1 And D.GC = 2 And D.PSATIR = 1 And D.ORDERNO = O.ORDERNO And D.MODEL = O.MODEL1 And D.TARIH = FLOOR(Cast(GETDATE() As Float))), 0.0) <> 0 Or isnull((Select Sum(D.B1 + D.B2 + D.B3 + D.B4 + D.B5 + D.B6 + D.B7 + D.B8 + D.B9 + D.B10 + D.B11 + D.B12 + D.B13 + D.B14 + D.B15 + D.B16 + D.B17 + D.B18 + D.B19 + D.B20 + D.B21 + D.B22 + D.B23 + D.B24 + D.B25 + D.B26 + D.B27 + D.B28 + D.B29 + D.B30) From DIKIM D Where D.TIP = 5 And D.GC = 2 And D.PSATIR = 1 And D.ORDERNO = O.ORDERNO And D.MODEL = O.MODEL1 And D.TARIH = FLOOR(Cast(GETDATE() As Float))), 0.0) <> 0 Or isnull((Select Sum((CB.B1 + CB.B2 + CB.B3 + CB.B4 + CB.B5 + CB.B6 + CB.B7 + CB.B8 + CB.B9 + CB.B10 + CB.B11 + CB.B12 + CB.B13 + CB.B14 + CB.B15 + CB.B16 + CB.B17 + CB.B18 + CB.B19 + CB.B20 + CB.B21 + CB.B22 + CB.B23 + CB.B24 + CB.B25 + CB.B26 + CB.B27 + CB.B28 + CB.B29 + CB.B30) * isnull(Case When CB.KOLI2 <> CB.KOLI Then (CB.KOLI2 - CB.KOLI + 1) Else 1 End, 1)) From CEKIB CB Where CB.ORDERNO = O.ORDERNO And CB.MODEL = O.MODEL1 And CB.TARIH = FLOOR(Cast(GETDATE() As Float))), 0.0) <> 0