procedure Start;
Begin
End;

Procedure Finish;
Begin
End;

Function ReadFilter(Var F: String): Boolean;
Begin
  F:= '';
  Result:= True;
End;

Function ReadSQL(W: String): String;
Begin
  Result:= ' Declare @HALIDEPO Table( ' +
       '   STOKID INT, ' +
       '   RENKID INT, ' +
       '   DESENID INT, ' +
       '   DEPO DECIMAL(18, 4), ' +
       '   BIRIMCARPAN DECIMAL(18, 4), ' +
       '   ADET DECIMAL(18, 4), ' +
	   '   RESERVE DECIMAL(18, 4) ' +
       ' ) ' +
       '  ' +
       ' INSERT INTO @HALIDEPO(STOKID, DEPO, RENKID, DESENID)   ' +
       ' Select STOKID, SUM(DEPO), RENKID, DESENID ' +
       ' FROM( ' +
       ' Select ' +
       '   D.STOKID,  ' +
       '   CASE WHEN ISLEMTIPI IN(2, 5, 53, 55, 10, 145, 157, 172, 174, 237, 238, 239, 240) THEN CIKISDEPO ELSE GIRISDEPO END AS DEPOID, ' +
       '   SUM(D.GIREN - D.CIKAN) / B.CARPAN AS DEPO, RENKID, DESENID ' +
       ' FROM STOKHAREKET D ' +
       ' LEFT OUTER JOIN TBLSTOKLAR S WITH(INDEX=TBLSTOKLAR_KARTNO) ON S.KARTNO = D.STOKID ' +
       ' LEFT OUTER JOIN TBLBIRIMLEREX B WITH(INDEX=TBLBIRIMLEREX_KARTNO) ON S.ANABIRIM = B.KARTNO ' +
       ' WHERE ISNULL(S.DELETED, 0) = 0 ' +
       ' GROUP BY D.STOKID, ISLEMTIPI, GIRISDEPO, CIKISDEPO, CARPAN, RENKID, DESENID ' +
       ' ) AS A ' +
       ' LEFT OUTER JOIN TBLSTOKLAR S WITH(INDEX=TBLSTOKLAR_KARTNO) ON S.KARTNO = A.STOKID ' +
       ' LEFT OUTER JOIN TBLRENKLER R ON R.ID = A.RENKID ' +
       ' LEFT OUTER JOIN TBLHALIDESENLER D ON D.ID = A.DESENID ' +
       ' GROUP BY STOKID, RENKID, DESENID ' +
       '  ' +
	   ' INSERT INTO @HALIDEPO(STOKID, DEPO, RENKID, DESENID) ' +
	   ' Select ' +
  	   '   S.KARTNO AS STOKID, 0, R.RENKID, R.DESENID ' +
	   ' FROM TBLRESERVE R ' +
	   ' LEFT OUTER JOIN TBLBIRIMLEREX B WITH(INDEX=TBLBIRIMLEREX_KARTNO) ON B.KARTNO = R.BIRIMID ' +
	   ' LEFT OUTER JOIN TBLSTOKLAR S WITH(INDEX=TBLSTOKLAR_KARTNO) ON S.KARTNO = B.STOKID ' +
	   ' LEFT OUTER JOIN TBLBIRIMLEREX BX WITH(INDEX=TBLBIRIMLEREX_KARTNO) ON BX.STOKID = S.KARTNO AND BX.BIRIMADI = ''M2'' ' +
	   ' WHERE ISNULL((SELECT COUNT(*) FROM @HALIDEPO WHERE STOKID = S.KARTNO AND (BIRIMID = BX.KARTNO OR BIRIMID = B.KARTNO) AND RENKID = R.RENKID AND DESENID = R.DESENID), 0) < 1 ' +
	   ' GROUP BY S.KARTNO, BX.KARTNO, R.RENKID, R.DESENID ' +
	   ' ' +
	   ' UPDATE @HALIDEPO SET RESERVE = (SELECT SUM(MIKTAR) FROM TBLRESERVE R  ' +
	   '    LEFT OUTER JOIN TBLBIRIMLEREX B WITH(INDEX=TBLBIRIMLEREX_KARTNO) ON B.KARTNO = R.BIRIMID ' +
	   '    LEFT OUTER JOIN TBLSTOKLAR S WITH(INDEX=TBLSTOKLAR_KARTNO) ON S.KARTNO = B.STOKID ' +
	   '    LEFT OUTER JOIN TBLBIRIMLEREX BX WITH(INDEX=TBLBIRIMLEREX_KARTNO) ON BX.STOKID = S.KARTNO AND BX.BIRIMADI = ''ADET'' ' +
	   '    WHERE  (BIRIMID = BX.KARTNO or BIRIMID = B.KARTNO) AND [@HALIDEPO].STOKID = S.KARTNO ' +
	   '    AND RENKID = [@HALIDEPO].RENKID AND DESENID = [@HALIDEPO].DESENID) ' +
	   ' ' +	   
       ' UPDATE @HALIDEPO SET BIRIMCARPAN = ISNULL((SELECT TOP 1 EN * BOY / 10000.000 FROM TBLBIRIMLEREX WHERE STOKID = [@HALIDEPO].STOKID AND BIRIMADI = ''ADET''), 1) ' +
       ' UPDATE @HALIDEPO SET ADET = DEPO / BIRIMCARPAN ' +
       '  ' +
       ' Select  ' +
       '   STOKKODU, CINSI, KOD1 AS MARKA, R.KOD AS RENK, DS.KOD AS DESEN, BIRIMCARPAN AS HALIM2, DEPO AS M2, ADET, RESERVE, ISNULL(ADET, 0) - ISNULL(RESERVE, 0) DEPOKALAN ' +
       ' from @HALIDEPO D ' + 
	   ' LEFT OUTER JOIN TBLSTOKLAR S WITH(INDEX=TBLSTOKLAR_KARTNO) ON S.KARTNO = D.STOKID ' +
       ' LEFT OUTER JOIN TBLRENKLER R ON R.ID = D.RENKID ' +
       ' LEFT OUTER JOIN TBLHALIDESENLER DS ON DS.ID = D.DESENID ' + 
	   ' ORDER BY CINSI';
End;

Function RaporDefaultSQL: String;
Begin
  Result:= ReadSQL('');
End;

Function RaporFileName: String;
Begin
  Result:= ParsDir + 'Halı\HaliDepoDurumu.fr3';
End;

procedure Execute;
Var
  IDS: String;
Begin
  if ReadFilter(IDS) Then
  Begin    
    CreateReport(IDS, ReadSQL(IDS), 
     'STOKKODU;CINSI;MARKA;RENK;DESEN;HALIM2:0;ADET;M2;RESERVE;DEPOKALAN', 
	 'Stok Kodu;Cinsi;Marka;Renk;Desen;Adet M2;Depo Adet;Depo M2;Reserve;Depo Kalan', 
	  0);
    CreateSummary('STOKKODU', skCount);
    CreateSummary('M2', skSum);  
    CreateSummary('ADET', skSum);  
	CreateSummary('DEPOKALAN', skSum);  
  End;
End;

Begin
End.
