IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QMUH_SATINALMAISL]') AND OBJECTPROPERTY(id,N'IsVIew') = 1)
BEGIN
    INSERT INTO SCRIPT_BACKUPS (SCRIPTNAME,SCRIPT,SCRIPTDATE) SELECT 'QMUH_SATINALMAISL' SCRIPTNAME,OBJECT_DEFINITION(OBJECT_ID('QMUH_SATINALMAISL')) AS SCRIPT,GETDATE() AS SCRIPTDATE
    DROP VIEW QMUH_SATINALMAISL
END
GO  

CREATE VIEW [dbo].[QMUH_SATINALMAISL] AS   
SELECT     dbo.FN_AYADI(MONTH(S.TARIH)) AS AYADI, SI.SATINALMAID, SI.ID AS SATINALMAISLID, SI.STOKID, SI.ACIKLAMA AS STOKACIKLAMA, SI.DEPOMEVCUT, SI.BIRIM,     
                      SI.TALEPMIKTAR, SI.ONAYMIKTAR, SI.DOVIZ, SI.BFIYAT, SI.SATICIID, SI.TESLIMSURE, SI.ODEMESURE, SI.IPTAL, SI.ISKONTOORAN, SI.ISKONTOORAN2,     
                      SI.ISKONTOORAN3, SI.ISKONTOTUTAR, SI.ONAYNOTLARI, SI.ONAYMIKTAR * SI.BFIYAT AS TUTAR, S.SUBEID, S.TARIH, S.DEPOID, S.USERID, S.ENO,     
                      S.ACIKLAMA AS BELGEACIKLAMA, S.EK1, S.EK2, S.DURUMID, SB.SUBEKODU, SB.SUBEADI, D.DEPOADI, U.KODU AS USERKODU, U.ADI AS USERADI, SD.DURUMADI,     
                      C.KODU AS HESAPKODU, C.UNVANI AS HESAPADI, ST.KODU AS STOKKODU, ST.ADI AS STOKADI, ST.GRUP AS STOKGRUP, STG.ADI AS STOKGRUPADI,     
                      SDEPO.ASGARI, SDEPO.KRITIK, SDEPO.OPTIMAL, CAST(NULL AS INT) AS ALTCARIID, CAST(NULL AS NVARCHAR(100)) AS ALTCARIADI, CAST(NULL AS NVARCHAR(50))     
                      AS ALTCARIKODU, SI.MARKAID, SMA.MARKAADI, SI.MARKA AS MARKAMANUEL,    
                          (SELECT     ISNULL(SUM(FATISL.FADET), 0) AS Expr1    
                            FROM          FATISL INNER JOIN    
                                                   FATURA ON FATURA.FATID = FATISL.FATID    
                            WHERE      (FATURA.IRSALIYE = 1) AND (FATISL.SATINALMAISLID = SI.ID)) AS IRSMIKTAR,    
       [dbo].[FNFATURAADETBUL](SI.ID) AS FATMIKTAR ,C.EMAIL ,S.DOVIZKODU, MS.STATUADI , S.TALEPFISID  ,
	   

    ISNULL(SI.ONAYMIKTAR,0)- ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0) CKALANSIPARISMIKTARI,

 case when ( ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0)=0) and (ISNULL(SI.ONAYMIKTAR,0)>0) then 'Hiç Gelmeyen Sipariş'
      when ( ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0)>0) and (ISNULL(SI.ONAYMIKTAR,0)> ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0)) then 'Kısmi Gelen Sipariş'
      when ISNULL(SI.ONAYMIKTAR,0)>0 AND ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0)>0 AND ISNULL(SI.ONAYMIKTAR,0)= ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0) then  'Tamamlanan Sipariş'
      when ISNULL(SI.ONAYMIKTAR,0)< ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0) then 'Fazla Gelen Sipariş'
 else 'Diğer'  end CSIPARISDURUM,
  case when ( ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0)=0) and (ISNULL(SI.ONAYMIKTAR,0)>0) then 1
      when (ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0)>0) and (ISNULL(SI.ONAYMIKTAR,0)>ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0)) then 2
      when ISNULL(SI.ONAYMIKTAR,0)>0 AND ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0)>0 AND  ISNULL(SI.ONAYMIKTAR,0)=ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0) then  3
      when ISNULL(SI.ONAYMIKTAR,0)< ISNULL([dbo].[FNFATURAADETBUL](SI.ID),0) then 4
 else 5  end CSIPARISDURUMID,
 dbo.[Fn_Satinalma_FaturaListesi] (SI.ID) FATURALISTESI


FROM         MUH_SATINALMAISL AS SI (NOLOCK) INNER JOIN    
                      MUH_SATINALMA AS S (NOLOCK) ON SI.SATINALMAID = S.ID LEFT OUTER JOIN    
                      STOK AS ST (NOLOCK) ON SI.STOKID = ST.STOKID LEFT OUTER JOIN    
                      STOKGRUP AS STG (NOLOCK) ON ST.GRUP = STG.KODU LEFT OUTER JOIN    
                      SUBELER AS SB (NOLOCK) ON S.SUBEID = SB.SUBEID LEFT OUTER JOIN    
                      [USER] AS U (NOLOCK) ON U.USERID = S.USERID LEFT OUTER JOIN    
                      DEPO AS D (NOLOCK) ON S.DEPOID = D.DEPOID LEFT OUTER JOIN    
                      STOKMARKA AS SMA (NOLOCK) ON SI.MARKAID = SMA.MARKAID LEFT OUTER JOIN    
                      ETS_CARI AS C (NOLOCK) ON SI.SATICIID = C.CARIID LEFT OUTER JOIN    
                      MUH_SATINALMADURUM AS SD (NOLOCK) ON S.DURUMID = SD.DURUMID LEFT OUTER JOIN    
                      SDEPO (NOLOCK) ON SI.STOKID = SDEPO.STOKID AND D.DEPOID = SDEPO.DEPOID LEFT OUTER JOIN  
       MUH_SATINALMASTATU AS MS (NOLOCK) ON S.STATUID=MS.STATUID  
WHERE     (ISNULL(SI.IPTAL, 0) = 0) AND (ISNULL(S.IPTAL, 0) = 0)    


