GO

/****** Object:  StoredProcedure [dbo].[MPOS_AdisyonYazdir]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter proc [dbo].[MPOS_AdisyonYazdir](@FISCOUNTER int)
as 
begin
 UPDATE FISARSIV SET DIRTY=0 WHERE FISCOUNTER=@FISCOUNTER
end
GO

/****** Object:  StoredProcedure [dbo].[MPos_BakiyeGoster]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter proc [dbo].[MPos_BakiyeGoster](@OdaNo Nvarchar(20),@KartNo as NVARCHAR(20))
as 
begin
  if  @OdaNo is not null and  @OdaNo<>'' 
   begin
     SELECT (SELECT VALUESTR FROM CONFIG WHERE KEYNAME LIKE 'Hesap Limitini Kontrol Et') AS LIMIT,KNO,ODANO,MADI1 AS MISAFIRADI,CIN.ODANO,CCTYPE AS VIP, CITARIHI AS GELIS, COUTTARIHI AS AYRILIS,
	  ISNULL((SELECT SUM(BORC*-1) FROM FOLYOISL AS F WHERE F.FOLYONO=CIN.KNO),0) AS BAKIYE 
	 FROM CIN WHERE ODANO=@OdaNo
   end
 else if  @KartNo is not null and  @KartNo<>'' 
   begin
     SELECT (SELECT VALUESTR FROM CONFIG WHERE KEYNAME LIKE 'Hesap Limitini Kontrol Et') AS LIMIT,R.KNO,RK.AD+' '+SOYAD AS MISAFIRADI,R.ODANO,CCTYPE AS VIP, R.CITARIHI AS GELIS, R.COUTTARIHI AS AYRILIS,
	  (SELECT ISNULL(SUM(BORC*-1),0) FROM FOLYOISL AS F WHERE F.FOLYONO=R.KNO AND F.KISINO=CAST(RK.SIRA AS NVARCHAR)) AS BAKIYE 
	  FROM RES AS R,REZKISI AS RK WHERE R.KNO=RK.KNO AND R.DURUM='I' AND ISNULL(RK.MKARTNO,'')=@KartNo
   end
end
GO

/****** Object:  StoredProcedure [dbo].[MPOS_Departman_MasaListesi]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter PROC [dbo].[MPOS_Departman_MasaListesi]
AS 
BEGIN
select D.KODU, D.ADI,
(
	SELECT M.MASANO AS MASANO, MASAID AS ID

	FROM MASALAR AS M

	WITH(NOLOCK)

	WHERE M.DEPART = D.KODU

	for xml path('MASA'), root('MASALAR'),TYPE

)
from DEPART AS D WITH(NOLOCK) where D.TUR = 'B' order by ADI

FOR XML PATH('DEPARTMAN'),ROOT('DEPARTMANLAR')
end
GO

/****** Object:  StoredProcedure [dbo].[MPOS_DepartmanList]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter PROC [dbo].[MPOS_DepartmanList]
as begin 
select KODU, ADI from DEPART where TUR = 'B' ORDER BY ADI
end
GO

/****** Object:  StoredProcedure [dbo].[MPOS_GarsonGiris]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter proc [dbo].[MPOS_GarsonGiris](@DepKod Nvarchar(20), @Sifre Nvarchar(20))
as 
 begin
 declare @maxusercount int
 declare @grupadi nvarchar(50)
 select @maxusercount=ERSPOS from SETUP

select @grupadi=GRUPADI from GARSONGRUP where GGRUPID = (select GGRUPID from Garson where GSIFRE = @Sifre)
if (@grupadi like '%SEF%' or @grupadi like '%şef%')
select @grupadi = 'SEF'
else if(@grupadi like '%GARSON%')
select @grupadi = 'GARSON'
else if(@grupadi like '%kasiyer%' or @grupadi like '%KASIYER%')
select @grupadi = 'KASIYER'
else if(@grupadi like '%MUTFAK%' or @grupadi like '%BAR%')
select @grupadi = 'MUTFAK'
else
select @grupadi = 'USER'

SELECT @maxusercount AS MAXUSERCOUNT,@grupadi as GRUPADI,* FROM GARSON WHERE GSIFRE=@Sifre AND (DEPKODU IS NULL OR DEPKODU LIKE '%'+@DepKod+'%')
     
 end 
GO

/****** Object:  StoredProcedure [dbo].[MPOS_GarsonGrupYetki]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



alter proc [dbo].[MPOS_GarsonGrupYetki](@GarsonGrupId int)
as 
 begin  
  SELECT DISTINCT ISLEMADI,GYETKIID,GGRUPID,CAST(YETKIVAR as int) AS YETKIVAR FROM GARSONGRUPYETKI WHERE GGRUPID=@GarsonGrupId      
 end 


GO

/****** Object:  StoredProcedure [dbo].[MPOS_HazirSiparisler]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter PROC [dbo].[MPOS_HazirSiparisler](@GARSONKODU as nvarchar(30))
as
--YENİ GÖNDERİLMİŞ ÜRÜNLER 
SELECT F.FISCOUNTER, FS.COUNTER AS SATIRCOUNTER, F.MASANO, S.ADI AS URUN, FS.ADET, FS.GONDERILDI 
   FROM ADISYON AS F, ADSSATIR AS FS, STOK AS S 
   WHERE F.FISCOUNTER=FS.FISCOUNTER AND FS.STOKID=S.STOKID 
   AND ISNULL(GONDERILDI,0)=1 AND ISNULL(FS.INDCODE,'')=''
   AND FS.GARSONKODU=@GARSONKODU 
GO

/****** Object:  StoredProcedure [dbo].[MPos_HesapGoster]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter proc [dbo].[MPos_HesapGoster](@DepKodu Nvarchar(20),@MasaNo as NVARCHAR(20))
as 
begin
  SELECT F.FISCOUNTER,F.TARIH,F.FISNO,F.MASANO, F.TOPLAM, F.STOPLAM, F.ASAATI, FS.STOKID AS URUNID,S.ADI AS URUNADI,FS.ADET, FS.BIRIMFIYAT,FS.ACIKLAMA, F.YAZCOUNT, FS.H, ISNULL(F.SEZLONG,0) AS SEZLONG, FS.DOVIZKODU, FS.DOVIZTUTAR, 
  (SELECT ISNULL(SUM(TUTAR),0) FROM ADISYONODEME WHERE ADISYONODEME.FISCOUNTER=ISNULL(F.FISCOUNTER,-1)) AS ODENEN
   
   FROM ADISYON AS F 
   LEFT JOIN ADSSATIR AS FS ON F.FISCOUNTER=FS.FISCOUNTER
   LEFT JOIN STOK AS S ON S.STOKID=FS.STOKID
WHERE F.DEPKODU=@DepKodu AND MASANO NOT LIKE 'Z%' AND (@MasaNo IS NULL OR F.MASANO=@MasaNo)    
end
GO

/****** Object:  StoredProcedure [dbo].[MPOS_HesapKapat]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--HesapKapat(DepId,MasaNo,OdemeTuru)
alter PROCEDURE [dbo].[MPOS_HesapKapat](@DepKodu Nvarchar(20),@MasaNo as NVARCHAR(20))
AS 
BEGIN   
    DECLARE @FISCCOUNTER INT 
    DECLARE @TOPLAMODEME FLOAT, @ADISYONTOPLAM FLOAT 
	
  SELECT @FISCCOUNTER=FISCOUNTER,@ADISYONTOPLAM=TOPLAM FROM ADISYON WHERE DEPKODU=@DepKodu AND MASANO=@MasaNo 
 
   IF (ISNULL(@FISCCOUNTER,0)=0) 
     RAISERROR('Masa Bulunamadı',16,1) 
   
   UPDATE F SET ONAKIT=NAKIT,
    OKK=KK,OACIK=ACIK,
    OTICKET=TICKET,
    OTICKET2=TICKET2,
    OTICKET3=TICKET3,
    OTICKET4=TICKET4,
    OTICKET5=TICKET5,
    OTICKET6=TICKET6,
    OTICKET7=TICKET7
	FROM FISARSIV AS F
    INNER JOIN (SELECT FISCOUNTER,
	SUM(CASE WHEN AOT.[ODEMETURU]='ONAKIT' THEN AO.TUTAR ELSE 0 END) AS NAKIT,
    SUM(CASE WHEN AOT.[ODEMETURU]='OKK' THEN TUTAR ELSE 0 END) AS KK,
    SUM(CASE WHEN AOT.[ODEMETURU]='OACIK' THEN TUTAR ELSE 0 END) AS ACIK,
    SUM(CASE WHEN AOT.[ODEMETURU]='OTICKET' THEN TUTAR ELSE 0 END) AS TICKET,
    SUM(CASE WHEN AOT.[ODEMETURU]='OTICKET2' THEN TUTAR ELSE 0 END) AS TICKET2,
    SUM(CASE WHEN AOT.[ODEMETURU]='OTICKET3' THEN TUTAR ELSE 0 END) AS TICKET3,
    SUM(CASE WHEN AOT.[ODEMETURU]='OTICKET4' THEN TUTAR ELSE 0 END) AS TICKET4,
    SUM(CASE WHEN AOT.[ODEMETURU]='OTICKET5' THEN TUTAR ELSE 0 END) AS TICKET5,
    SUM(CASE WHEN AOT.[ODEMETURU]='OTICKET6' THEN TUTAR ELSE 0 END) AS TICKET6,
    SUM(CASE WHEN AOT.[ODEMETURU]='OTICKET7' THEN TUTAR ELSE 0 END) AS TICKET7 
	FROM ADISYONODEME AS AO,ADISYONODEMETURU AS AOT WHERE AO.ODEMETURID=AOT.ID AND AO.FISCOUNTER=@FISCCOUNTER
	 GROUP BY FISCOUNTER) AS AO ON AO.FISCOUNTER=F.FISCOUNTER
  WHERE F.FISCOUNTER=@FISCCOUNTER
  
  IF (ABS(@TOPLAMODEME-@ADISYONTOPLAM)>0.01) 
     RAISERROR('Yapılan Ödeme, Adisyon Toplamına Eşit Değil. MASA KAPANAMAZ.',16,1)    
  
  DECLARE @ORAN FLOAT 

  SELECT @ORAN=MAX(OACIK/TOPLAM) FROM ADISYON WHERE FISCOUNTER=@FISCCOUNTER

  IF (@ORAN)>0.00
    BEGIN 
	   IF (SELECT ISNULL(MAX(COUNTER),0) FROM FOLYOISL WHERE ADISYON=@FISCCOUNTER)>0
		 BEGIN
		   DELETE FROM FOLYOISL WHERE ADISYON=@FISCCOUNTER AND FOLYONO IN(SELECT KNO FROM CIN)
		 END


		---ACIKLARI İŞLE
		INSERT INTO FOLYOISL (GELIRGRUBU,FOLYONO,ILKKNO,DEPT,BORC,DOVIZTUTAR,DOVIZKODU,INDTUTAR,IADE,TARIH,ADISYON,KISINO,
		SAAT,YIYECEK,ICECEK,YETKILI,ENO,NOTE,RECID)
		SELECT GELIRGRUP.KODU, ADISYON.MKNO,ADISYON.MKNO,ADISYON.DEPKODU,(SUM(ADSSATIR.ADET*ADSSATIR.BIRIMFIYAT*(1-ISNULL(ADSSATIR.INDORAN,0))))*1,
		(SUM(ADSSATIR.ADET*ADSSATIR.BIRIMFIYAT*(1-ISNULL(ADSSATIR.INDORAN,0))))*@ORAN, 'TL',
		SUM((ADSSATIR.ADET*ADSSATIR.BIRIMFIYAT)  - ((((ADSSATIR.ADET * ADSSATIR.BIRIMFIYAT) * (1 - ADSSATIR.INDORAN)) * (100 - ISNULL(ADISYON.INDORAN,0)) / 100) * (100 + ISNULL(ADISYON.SERVISORAN,0)) / 100))*@ORAN AS INDTUTAR,
		CASE WHEN SUM(ADSSATIR.ADET*ADSSATIR.BIRIMFIYAT) >=0 THEN '' ELSE 'E' END AS TUR,ADISYON.TARIH,ADISYON.FISCOUNTER,
		FKISINO,ADISYON.KSAATI,0,0,LEFT(ADISYON.YETKILIKODU,10),ADISYON.FISNO,LEFT(ADISYON.MUSTERIADI,60),111
		FROM ADISYON,ADSSATIR,GELIRGRUP,STOK
		WHERE ADISYON.FISCOUNTER=ADSSATIR.FISCOUNTER
		AND ADSSATIR.STOKID=STOK.STOKID AND STOK.GGRUP=GELIRGRUP.KODU
		 AND ADSSATIR.FISCOUNTER=@FISCCOUNTER --AND OACIK>0
		GROUP BY GELIRGRUP.KODU,MKNO,DEPKODU,TARIH,ADISYON.FISCOUNTER,KSAATI,YETKILIKODU,FISNO,MUSTERIADI, FKISINO
	END

	  UPDATE ADISYON SET MASANO='Z'+@MasaNo WHERE FISCOUNTER=@FISCCOUNTER
   
   SELECT 'Masa Kapandi'
END 
GO

/****** Object:  StoredProcedure [dbo].[MPOS_LisansKontrol]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter proc [dbo].[MPOS_LisansKontrol]
as
BEGIN
--FAHRETTİN UYGUN 24.07.2018
declare @moduls nvarchar(50)
select @Moduls=MODULS from SETUP
if((select COUNT(*) from [dbo].[FN_SPLIT_STRING](@moduls,':') where splitdata like '%ERSPOS%')>0)
select 1 as Lisans
else
select 0 as Lisans

END
GO

/****** Object:  StoredProcedure [dbo].[MPOS_MasaListesi]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter proc [dbo].[MPOS_MasaListesi](@DepKod Nvarchar(20))
as 
begin
SELECT * FROM (
SELECT M.MASANO,M.MASAGRUP,M.STATU,A.FISCOUNTER,A.TOPLAM,A.ASAATI,A.YAZCOUNT,A.SEZLONG,(SELECT COUNT(*) FROM ADSSATIR WHERE ADSSATIR.FISCOUNTER=ISNULL(A.FISCOUNTER,0)) AS SATIRSAYI FROM MASALAR AS M
  LEFT JOIN ADISYON AS A ON A.MASANO=M.MASANO AND M.DEPART=A.DEPKODU
WHERE M.DEPART=@DepKod
UNION ALL 
SELECT A.MASANO,'' AS MASAGRUP,1 AS STATU,A.FISCOUNTER,A.TOPLAM,A.ASAATI,A.YAZCOUNT,A.SEZLONG,(SELECT COUNT(*) FROM ADSSATIR WHERE ADSSATIR.FISCOUNTER=ISNULL(A.FISCOUNTER,0)) AS SATIRSAYI  FROM  ADISYON AS A  
WHERE A.DEPKODU=@DepKod AND A.MASANO NOT LIKE 'Z%' AND A.MASANO NOT IN(SELECT M.MASANO FROM MASALAR AS M WHERE M.DEPART=@DepKod)
) AS TBL 
ORDER BY MASANO
end
GO

/****** Object:  StoredProcedure [dbo].[MPOS_MasaTransfer]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter PROC [dbo].[MPOS_MasaTransfer](@EskiMasaNo as NVARCHAR(20), @EskiMasaDepKodu as NVARCHAR(20), @YeniMasaNo as NVARCHAR(20),@YeniMasaDepKodu as NVARCHAR(20))
 as 
begin 

DECLARE @FISCOUNTER int
SELECT @FISCOUNTER=FISCOUNTER FROM ADISYON WHERE MASANO=@EskiMasaNo AND DEPKODU=@EskiMasaDepKodu

UPDATE ADISYON SET 
    MASANO=@YeniMasaNo, 
	DEPKODU=@YeniMasaDepKodu, 
	ACIKLAMA=LEFT(ISNULL(ACIKLAMA,'')+@EskiMasaNo+' NOLU MASADAN GELDI',50) 
 WHERE FISCOUNTER =@FISCOUNTER

end
GO

/****** Object:  StoredProcedure [dbo].[MPOS_MasaYetkilisi]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--Fahrettin UYGUN 23 temmuz 2018
alter proc [dbo].[MPOS_MasaYetkilisi](@GARSONID nvarchar(20), @MASANO nvarchar(20))
as
BEGIN
DECLARE @VALUE INT
DECLARE @GARSONKODU NVARCHAR(20)
DECLARE @GKODU NVARCHAR(20)
SELECT @VALUE=VALUESTR FROM CONFIG WHERE KEYNAME LIKE 'Garson Sadece Kendi Masasina Mudahele Edebilir'
SELECT @GKODU=GKODU FROM GARSON WHERE ID=@GARSONID
IF (@VALUE=NULL AND @VALUE=0)
select 1 as SONUC
ELSE
	BEGIN
	--Boş masalarda satır dönmediği için sonuç 0 dönüyordu. O yüzden masanın boş olup olmadığı durumu kontrol edildi
	-- Eğer boşsa direkt sanuç olarak 1 gönderildi. // 26.07.2018
	if((SELECT COUNT(GARSONKODU) AS SATIR FROM ADISYON WHERE MASANO NOT LIKE 'Z%' AND MASANO=@MASANO)=0)
	select 1 as SONUC
	else
	begin
	SELECT @GARSONKODU=GARSONKODU FROM ADISYON WHERE MASANO NOT LIKE 'Z%' AND MASANO=@MASANO
		IF(@GKODU=@GARSONKODU)
			select 1 as SONUC
		ELSE
			select 0 as SONUC
	end
	END
END
GO

/****** Object:  StoredProcedure [dbo].[MPOS_ModifySignalRID]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter PROC [dbo].[MPOS_ModifySignalRID] @GarsonKodu nvarchar(15),@SignalRID nvarchar(50)
as begin

update GARSON SET SIGNALID = @SignalRID where ID = @GarsonKodu
end
GO

/****** Object:  StoredProcedure [dbo].[MPOS_OdemeEkle]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[MPOS_OdemeEkle](@DepKodu Nvarchar(20),@MasaNo as NVARCHAR(20),@OdemeTuru nvarchar(20),@Tutar Float,@KartNo NVARCHAR(30)='',@OdaNo NVARCHAR(30)='')
AS 
BEGIN   
    DECLARE @FISCCOUNTER INT 
	
  SELECT @FISCCOUNTER=FISCOUNTER FROM ADISYON WHERE DEPKODU=@DepKodu AND MASANO=@MasaNo 
 
   IF (ISNULL(@FISCCOUNTER,0)=0) 
     RAISERROR('Masa Bulunamadı',16,1) 
   
   INSERT INTO ADISYONODEME (FISCOUNTER,ODEMETURID,SAAT,TUTAR)
   SELECT @FISCCOUNTER,ID,GETDATE(),@Tutar FROM ADISYONODEMETURU WHERE ODEMEADI=@OdemeTuru
   
   
   IF (@KartNo<>'') AND  (SELECT TOP 1 ODEMETURU FROM ADISYONODEMETURU WHERE ODEMEADI=@OdemeTuru)='OACIK'
    BEGIN
	  UPDATE ADISYON SET MUSKARTNO=@KartNo,MKNO=T.KNO,MODAADI=T.MISAFIR,MODANO=T.ODANO,FKISINO=T.SIRA FROM ADISYON,
	  (SELECT TOP 1 REZKISI.SIRA, RES.ODANO,RES.KNO,REZKISI.AD+' '+REZKISI.SOYAD AS MISAFIR FROM RES,REZKISI WHERE RES.KNO=REZKISI.KNO AND DURUM='I' AND ISNULL(MKARTNO,'')=@KartNo) AS T   
	  WHERE FISCOUNTER=@FISCCOUNTER
	END
      ELSE
  IF (@OdaNo<>'') AND  (SELECT TOP 1 ODEMETURU FROM ADISYONODEMETURU WHERE ODEMEADI=@OdemeTuru)='OACIK'
    BEGIN
	  UPDATE ADISYON SET MKNO=T.KNO,MODAADI=T.MISAFIR,MODANO=T.ODANO,FKISINO=T.SIRA FROM ADISYON,
	  (SELECT TOP 1 REZKISI.SIRA, RES.ODANO,RES.KNO,REZKISI.AD+' '+REZKISI.SOYAD AS MISAFIR FROM RES,REZKISI WHERE RES.KNO=REZKISI.KNO AND DURUM='I' AND ISNULL(RES.ODANO,'')=@OdaNo) AS T   
	  WHERE FISCOUNTER=@FISCCOUNTER
	END
   SELECT 'Ödeme Eklendi'
END 
GO

/****** Object:  StoredProcedure [dbo].[MPOS_OdemeTurleri]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter PROC [dbo].[MPOS_OdemeTurleri]
as 
begin
  SELECT CASE WHEN ODEMETURU='ONAKIT' THEN 'Nakit' 
			  WHEN ODEMETURU='OKK' THEN 'Kredi Karti' 
			  WHEN ODEMETURU='OACIK' THEN 'Hesaptan Ödeme'
			  WHEN ODEMETURU LIKE 'OTICKET%' THEN 'Ticket' END ODEMETURU, ODEMEADI, DEPT FROM [ADISYONODEMETURU] ORDER BY ODEMETURU
end
	
GO

/****** Object:  StoredProcedure [dbo].[MPOS_Parametreler]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



alter PROC [dbo].[MPOS_Parametreler]
AS
BEGIN
DECLARE @USECALLGARSON VARCHAR(50) = '0';
DECLARE @USEADD VARCHAR(50)= '1';

select
(SELECT ISNULL(VALUESTR ,'0') FROM CONFIG WHERE KEYNAME='Sezlong Kullanimi Aktif') AS  USESEZLONG,
(select ISNULL(VALUESTR ,'0') from CONFIG WHERE KEYNAME='Otel/Restoran/Uye (0/1/2)') AS UYGULAMAMODU,
(SELECT ISNULL(VALUESTR ,'0') FROM CONFIG WHERE KEYNAME LIKE 'Kart No Kullan')AS USECART,

-- CONFIG tablosuna ilgili kolon eklenecek unutma @ue
(SELECT @USEADD) AS USEADD, 
(SELECT @USECALLGARSON) AS USECALLGARSON
END
GO

/****** Object:  StoredProcedure [dbo].[MPOS_UpdateNotification]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




alter proc [dbo].[MPOS_UpdateNotification] @SATIRCOUNTER as int
as
UPDATE ADSSATIR SET INDCODE='OK' FROM ADSSATIR WHERE COUNTER=@SATIRCOUNTER
GO

/****** Object:  StoredProcedure [dbo].[MPOS_UrunEkle]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[MPOS_UrunEkle](@DepKodu Nvarchar(20),@MasaNo NVARCHAR(20),@UrunId INT,@Adet FLOAT,@Fiyat Float,@DovizKodu Nvarchar(10),
		@Ozellik NVARCHAR(50),@GarsonId INT, @SezlongDurum Bit=0, @KisiNo Int=0)
AS
BEGIN 
  DECLARE @FISCOUNTER INT 

  SELECT @FISCOUNTER=FISCOUNTER FROM ADISYON WHERE ISNULL(DEPKODU,'')=@DepKodu AND ISNULL(MASANO,'')=@MasaNo 

  IF (ISNULL(@FISCOUNTER,0)=0)
   BEGIN

     EXEC SPREADID2 'FISARSIV','FISCOUNTER',@FISCOUNTER OUT

	 INSERT INTO ADISYON (FISCOUNTER,DEPKODU,TARIH,MASANO,GARSONKODU,ASAATI,TUR,KISI,FISLI) 
	  VALUES(@FISCOUNTER,@DepKodu,(SELECT TARIH FROM PTARIH),@MasaNo,(SELECT GKODU FROM GARSON WHERE ID=@GarsonId),GETDATE(),'SATIS',1,1)

   if ISNULL(@SezlongDurum,0)=1 
    BEGIN 
	  DECLARE @SemsiyeUrunId int,@SemsiyeFiyat Float 
	   SELECT @SemsiyeUrunId= STOKID,@SemsiyeFiyat=SFIYAT1 FROM STOK WHERE KODU=DBO.FN_READEXEINI('FPOS','Masalar','Sezlong Urun Kodu')      
	   INSERT INTO ADSSATIR (FISCOUNTER,STOKID,ADET,BIRIMFIYAT,ACIKLAMA,GARSONKODU,SAAT, GGRUP,SIP, H,INDORAN)
	   VALUES(@FISCOUNTER,@SemsiyeUrunId, 1,@SemsiyeFiyat,'',
	   (SELECT top 1 GKODU FROM GARSON WHERE ID=@GarsonId),
	   GETDATE(),
	   (SELECT top 1 GGRUP FROM STOK WHERE STOK.STOKID=@SemsiyeUrunId),0,@KisiNo,0)
	END
	
   END 

   DECLARE @TLFiyat Float
   
   if ISNULL(@DovizKodu,'')='' 
     SELECT @DovizKodu=dbo.FN_READEXEINI('FPOS','TL', 'Ana Para Birimi')

   if (select dbo.FN_READEXEINI('FPOS','Adisyon','Adisyon Doviz Kur Tipi'))='KUR3'  
     SET @TLFiyat=@Fiyat*dbo.FNGET_KUR((SELECT TARIH FROM PTARIH),@DovizKodu,3)
   else if (select dbo.FN_READEXEINI('FPOS','Adisyon','Adisyon Doviz Kur Tipi'))='KUR2'  
     SET @TLFiyat=@Fiyat*dbo.FNGET_KUR((SELECT TARIH FROM PTARIH),@DovizKodu,2)
   else if (select dbo.FN_READEXEINI('FPOS','Adisyon','Adisyon Doviz Kur Tipi'))='KUR1'  
     SET @TLFiyat=@Fiyat*dbo.FNGET_KUR((SELECT TARIH FROM PTARIH),@DovizKodu,1)
   else
    SET @TLFiyat=@Fiyat*dbo.FNGET_KUR((SELECT TARIH FROM PTARIH),@DovizKodu,4) 
	

	-- TLFiyat virgülden sonra iki hane göstermesi için round kodu eklendi // Fahrettin UYGUN

   INSERT INTO ADSSATIR (FISCOUNTER,STOKID,ADET,BIRIMFIYAT,ACIKLAMA,GARSONKODU,SAAT, GGRUP,SIP, H,INDORAN,DOVIZKODU,DOVIZTUTAR)
   VALUES(@FISCOUNTER,@UrunId, @Adet,ROUND(@TLFiyat,2),@Ozellik,
   (SELECT top 1 GKODU FROM GARSON WHERE ID=@GarsonId),
   GETDATE(),
   (SELECT top 1 GGRUP FROM STOK WHERE STOK.STOKID=@UrunId),0,@KisiNo,0,  @DovizKodu,@Fiyat)
  
  UPDATE ADISYON SET 
    TOPLAM=  (SELECT ISNULL(SUM(ADET*BIRIMFIYAT*(1-ISNULL(INDORAN,0)))*(1-ISNULL(ADISYON.INDORAN,0)/100),0) FROM ADSSATIR WHERE ADSSATIR.FISCOUNTER=ADISYON.FISCOUNTER),
    STOPLAM= (SELECT ISNULL(SUM(ADET*BIRIMFIYAT),0) FROM ADSSATIR WHERE ADSSATIR.FISCOUNTER=ADISYON.FISCOUNTER),
	SEZLONG= @SezlongDurum,
	SERVISORAN=ISNULL(SERVISORAN,0),
	INDORAN=ISNULL(INDORAN,0), 
	INDTUTAR=ISNULL(INDTUTAR,0)  	
	FROM ADISYON WHERE FISCOUNTER=@FISCOUNTER

  SELECT FISCOUNTER,MASANO FROM ADISYON WHERE FISCOUNTER=@FISCOUNTER

END


GO

/****** Object:  StoredProcedure [dbo].[MPOS_URUNLIST]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter PROCEDURE [dbo].[MPOS_URUNLIST](@DEPKODU NVARCHAR(5))
AS

 SELECT
       G.[KODU] AS [GKODU],
       G.[ADI]  AS [GADI],
       (
             SELECT
			 -- KALORI, HAZIRLIK, PISIRMESURESI, SUNUMSURESI, SUNUMSICAKLIK, STOKREESIM @UGUR
                    S.ADI     AS [SADI],
                    S.[STOKID]AS [SID],
					S.[KALORI] AS [KAL],
					S.[HAZIRLIK] AS [DES],
					CONVERT (nvarchar,S.[PISIRMESURE] )AS [PSURE],					
					CONVERT (nvarchar,S.[SUNUMSURE])AS [SSURE],					
					CONVERT (nvarchar,S.[SUNUMSICAKLIK] )AS [SSCK],					
					S.[STOKRESIM] AS [SR],
					ISNULL(S.VITRINRESMIMI,0) AS [VR],           
					FORMAT(S.SFIYAT1,'#0.00') AS [F],
                    ISNULL(S.SDOVIZ,'TL') AS [D],
					S.SDEPART AS DEP,
                    (
                           SELECT
                                 F.DEPART AS [DEP],
                                 FORMAT(F.FIYAT,'#0.00')  AS [F],
                                 ISNULL(F.DOVIZK,'TL') AS [D]
                           FROM [UFIYAT] F WITH(NOLOCK)
                           WHERE F.[STOKID] = S.[STOKID] AND  ( CAST(GETDATE() AS TIME) BETWEEN CAST(ISNULL(BAS,GETDATE()) AS TIME) AND CAST(ISNULL(BITIS,GETDATE()) AS TIME))
                           FOR XML PATH('FIYAT'),ROOT('FIYATLAR'),TYPE
                    ),
                    (
                           SELECT
                                 O.ADI,
								 FORMAT( O.FIYATI,'#0.00') AS [OFYT]
                           FROM [OZELLIK] O WITH(NOLOCK)
                           WHERE O.[STOKID] = S.[STOKID]
                           FOR XML PATH('EKSTRA'),ROOT('EKSTRALAR'),TYPE
                    )

             FROM [STOK] S WITH(NOLOCK)
             WHERE S.GRUP = G.KODU AND SDEPART LIKE '%'+@DEPKODU+'%' ORDER BY [SADI]
             FOR XML PATH('STOK'),ROOT('STOKLAR'),TYPE
       )
FROM [STOKGRUP] G WITH(NOLOCK)
WHERE G.[TURU] COLLATE TURKISH_CI_AI = 'URUN' AND ((@DEPKODU IS NULL) OR  (G.KODU IN(SELECT DISTINCT GRUP FROM STOK WHERE SDEPART LIKE '%'+@DEPKODU+'%')))
ORDER BY [GADI]
FOR XML PATH('GRUP'),ROOT('GRUPLAR')


GO

/****** Object:  StoredProcedure [dbo].[MPOS_UrunOlustur]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--FAHRETTİN UYGUN 02.08.18 // HATALI PROSEDÜR
alter proc [dbo].[MPOS_UrunOlustur] ( 
@StokGrupId nvarchar(50),
@Name nvarchar(50), 
@Fiyat float, 
@ParaBirimi nvarchar(15),
@StokResim nvarchar(100),
@Kalori nvarchar(50),
@Aciklama nvarchar(500),
@Departman nvarchar(50),
@PosTuru nvarchar(50),
@GGrup nvarchar(50)
)
 as BEGIN

declare @ID int
declare @MAXKODU int
select @MAXKODU=MAX(KODU) from STOK where GRUP=@StokGrupId
select @ID=SIRANO from SAYACLAR where TABLENAME='STOK'
Update SAYACLAR set SIRANO=@ID+1 where TABLENAME='STOK'
INSERT INTO STOK (GRUP,ADI,STOKID,KODU,SFIYAT1,BIRIM,STOKRESIM,KALORI,EKBILGI,SDEPART,TURU, POSTURU, GGRUP)
values (@StokGrupId,@Name,(@ID+1),@MAXKODU,@Fiyat,@ParaBirimi,@StokResim,@Kalori,@Aciklama,@Departman,'MAMUL',@PosTuru,@GGrup)
end
GO

/****** Object:  StoredProcedure [dbo].[MPOS_UrunTransfer]    Script Date: 29.08.2018 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter PROC [dbo].[MPOS_UrunTransfer](@EskiMasaNo as NVARCHAR(20), @YeniMasaNo as NVARCHAR(20),@StokId int, @Adet Float)
 as 
begin 

DECLARE @EskiFISCOUNTER int, @YeniFISCOUNTER int, @HataMsg nvarchar(100)

if  @Adet=0
 begin 
  raiserror('Transfer Edilecek Adet 0 Olamaz.',16,0)
end


if (select count(*) FROM ADISYON WHERE MASANO=@EskiMasaNo)=0
 begin 
  Set @HataMsg=@EskiMasaNo+' Nolu Masa Sistemde Yok'
  raiserror(@HataMsg,16,0)
end

if (select count(*) FROM ADISYON WHERE MASANO=@YeniMasaNo)=0 
 begin 
  Set @HataMsg=@YeniMasaNo+' Nolu Masa Sistemde Yok'
  raiserror(@HataMsg,16,0)
end

SELECT @EskiFISCOUNTER=FISCOUNTER FROM ADISYON WHERE MASANO=@EskiMasaNo 
SELECT @YeniFISCOUNTER=FISCOUNTER FROM ADISYON WHERE MASANO=@YeniMasaNo 

declare @SatirCounter int, @YeniSatirCounter int 

SELECT TOP 1 @SatirCounter=COUNTER FROM ADSSATIR WHERE FISCOUNTER=@EskiFISCOUNTER AND STOKID=@StokId AND ADET>=@Adet 

if ISNULL(@SatirCounter,0)=0 
 begin 
  Set @HataMsg=@YeniMasaNo+' Nolu Masada '+(select TOP 1 ADI FROM STOK WHERE STOKID=@StokId)+' Ürün Yok'
  raiserror(@HataMsg,16,0)
 end

INSERT INTO ADSSATIR (FISCOUNTER,ILKFISCOUNTER,STOKID,ADET,BIRIMFIYAT,H,HHOUR,ACIKLAMA,INDID,INDORAN,SIP,YAZ,SAAT,GGRUP,ZAYII,GONDERILDI)
SELECT @YeniFISCOUNTER,@EskiFISCOUNTER,STOKID,@Adet,BIRIMFIYAT,H,HHOUR,ACIKLAMA,INDID,INDORAN,SIP,YAZ,SAAT,GGRUP,ZAYII,GONDERILDI FROM ADSSATIR WHERE COUNTER=@SatirCounter

select @YeniSatirCounter=SCOPE_IDENTITY()

IF (SELECT ADET-@Adet FROM ADSSATIR WHERE COUNTER=@SatirCounter)=0 
  DELETE FROM ADSSATIR WHERE COUNTER=@SatirCounter
ELSE
  UPDATE ADSSATIR SET ADET=ADET-@Adet WHERE COUNTER=@SatirCounter

  UPDATE ADISYON SET 
    TOPLAM=  (SELECT ISNULL(SUM(ADET*BIRIMFIYAT*(1-ISNULL(INDORAN,0)))*(1-ISNULL(ADISYON.INDORAN,0)/100),0) FROM ADSSATIR WHERE ADSSATIR.FISCOUNTER=ADISYON.FISCOUNTER),
    STOPLAM= (SELECT ISNULL(SUM(ADET*BIRIMFIYAT),0) FROM ADSSATIR WHERE ADSSATIR.FISCOUNTER=ADISYON.FISCOUNTER)
	where FISCOUNTER in(@EskiFISCOUNTER,@YeniFISCOUNTER)
	

SELECT * FROM ADSSATIR WHERE COUNTER=@YeniSatirCounter

end
GO