Var
  ADOConn: TADOConnection;
  FSQLS: TStringList;
  
procedure Start;
Begin
  ADOConn:= TADOConnection.Create(nil);
  ADOConn.LoginPrompt:= False;
  ADOCOnn.ConnectionString:= ConnectionString;
	 
  ADOCOnn.Connected:= True;
  
  FSQLS:= TStringList.Create;
  FSQLS.Add('Set DateFormat DMY');
  FSQLS.Add('Declare @TempTable TABLE(CARIID INT, VADE DATETIME, SATIS DECIMAL(18, 4), ODEME DECIMAL(18, 4), BORC DECIMAL(18, 4))');
  FSQLS.Add('');
End;

Function CreateQuery: TADOQuery;
Begin
  Result:= TADOQuery.Create(ADOCOnn);
  Result.Connection:= ADOConn;
End;

Procedure Finish;
Begin
  ADOCOnn.Connected:= False;
  ADOConn.Free;
  
  FSQLS.Free;
End;

procedure CalculateCari(ID: Integer; AcHesap, Satis, Odeme: Single);
Var
  Q: TADOQuery;
  F: TStringList;
Begin
  if AcHesap <= 0 Then  
    Exit;
	
  Q:= CreateQuery;
  Q.SQL.Text:= 'SET DATEFORMAT DMY Select D.KARTNO, BORC AS TUTARI, D.VADE FROM CARIDOKUM D ' +
	 ' WHERE BORC > 0 AND D.CARIID = ' + IntToStr(ID) + 
	 ' ORDER BY D.VADE DESC';

  Q.Active:= True;
  
  Q.First;
  SaveDecimalSeperator;
  F:= TStringList.Create;
  
  F.Add('Set DateFormat DMY');
  F.Add('Declare @MinDate DateTime');
  F.Add('Declare @TempTable TABLE(KARTNO INT, KALAN DECIMAL(18, 4), VADE DATETIME)');
  
  While not Q.EOF Do
  Begin
    if Q.FieldByName('TUTARI').AsFloat > 0 Then
	Begin
      if Q.FieldByName('TUTARI').AsFloat > AcHesap Then
	  Begin
	    F.Add('INSERT INTO @TempTable(KARTNO, KALAN, VADE) VALUES(' +
  	      Q.FieldByName('KARTNO').AsString + ',' + 
		  FormatFloat('#.#0', AcHesap) + ',' + 
		  QuotedStr(FormatDateTime('dd.mm.yyyy', Q.FieldByName('VADE').AsDateTime)) +
		  ')');
	    AcHesap:= 0;
	  End
	  Else
	  Begin
	    F.Add('INSERT INTO @TempTable(KARTNO, KALAN, VADE) VALUES(' +
	      Q.FieldByName('KARTNO').AsString + ',' + 
		  FormatFloat('#.#0', Q.FieldByName('TUTARI').AsFloat) + ',' + 
		  QuotedStr(FormatDateTime('dd.mm.yyyy', Q.FieldByName('VADE').AsDateTime)) +
		  ')');
	    AcHesap:= AcHesap - Q.FieldByName('TUTARI').AsFloat;
	  End;
	End;
	
	if AcHesap <= 0 Then
	  break;
    Q.Next;
  End;
  
  F.Add('Set @MinDate = (Select MIN(VADE) FROM @TempTable) - 1');
  F.Add('Select');
  F.Add('  @MinDate + CAST((SUM(CAST(VADE - @MinDate AS INT) * KALAN) / CASE SUM(KALAN) WHEN 0 THEN 1 ELSE SUM(KALAN) END) AS INT) AS VADE,');
  F.Add('  SUM(KALAN) AS BORC');
  F.Add('from @TempTable T');
  
//  F.Add('-- Cari ID = ' + IntToStr(ID));
//  if ID = 199 Then
//    F.SaveToFile('D:\Varsayilan.SQL');
  
  Q.Active:= False;
  Q.SQL.Text:= F.Text;
  Q.Active:= True;
  
  FSQLS.Add('INSERT INTO @TempTable(CARIID, VADE, SATIS, ODEME, BORC) VALUES(' + 
    IntToStr(ID) + ',' + 
	QuotedStr(FormatDateTime('dd.mm.yyyy', Q.FieldByName('VADE').AsDateTime)) + ',' +
	FormatFloat('#.#0', Satis) + ',' + 
	FormatFloat('#.#0', Odeme) + ',' +
	FormatFloat('#.#0', Q.FieldByName('BORC').AsFloat) + 
	')');
	  
  F.Free;
  UpdateDecimalSeperator;
  Q.Active:= False;
  Q.Free;
End;

procedure CheckSatis;
Var
  Q: TADOQuery;
Begin
  Q:= CreateQuery;
  
  Q.SQL.Text:= 'Select A.*, BORC - ALACAK AS BAKIYE FROM( ' + 
      ' Select CARIID, SUM(ROUND(ALACAK, 2)) AS ALACAK, SUM(ROUND(BORC, 2)) AS BORC ' + 
	  ' from CARIDOKUM  GROUP BY CARIID) AS A  WHERE BORC - ALACAK > 0.01';

  Q.Active:= True;
  Q.First;
  While not Q.EOF Do
  Begin
    if Q.FieldByName('BAKIYE').AsFloat > 0 Then 
	  CalculateCari(Q.FieldByName('CARIID').AsInteger, 
	      Q.FieldByName('BAKIYE').AsFloat,
	      Q.FieldByName('BORC').AsFloat, 
		  Q.FieldByName('ALACAK').AsFloat);
	
    Q.Next;
  End;
  
  Q.Active:= False;
  Q.Free;
End;
  
procedure RaporLoad;
Begin
  SetQuerySQL('KullaniciQ', FFilter);
End;

Function RaporFileName: String;
Begin
  Result:=  ParsDir + 'Cari Kart Raporları\Borç Raporları\VarsayılanVadeliBorc.Fr3';
End;

Function RaporDefaultSQL: String;
Begin
//  pxShowMessageBox('Rapo', 'Def SQL');
  Result:= 'Set DateFormat DMY '#13#10 + 
     ' Declare @TempTable TABLE(CARIID INT, VADE DATETIME, SATIS DECIMAL(18, 4), ODEME DECIMAL(18, 4), BORC DECIMAL(18, 4))'#13#10 + 
     ' Select'#13#10 + 
     ' C.FIRMAKODU, C.FIRMAADI, C.KOD1, C.KOD2, C.KOD3, C.KOD4, C.KOD5,'#13#10 + 
     ' T.SATIS, T.ODEME, T.BORC, T.VADE'#13#10 + 
     ' from @TempTable T'#13#10 + 
     ' LEFT OUTER JOIN TBLCARI C WITH(INDEX=TBLCARI_KARTNO) ON C.KARTNO = T.CARIID';
End;

Function ReadFilter: String;
Begin
End;

Function ReadSQL(W: String): String;
Begin
End;
  
procedure Execute;
Begin
  CheckSatis;
    
  FSQLS.Add('');
  FSQLS.Add('Select A.*, A.BORC - A.VADESIGELMEYENSATIS AS VADESIGECENSATIS FROM(');
  FSQLS.Add('Select');
  FSQLS.Add('  C.BAKIYE, C.FIRMAKODU, C.FIRMAADI, C.POSTAKODU, C.SEHIR, C.ULKE, C.KOD1, C.KOD2, C.KOD3, C.KOD4, C.KOD5, P.AD + '' '' + P.SOYAD AS PERSONEL,');
  FSQLS.Add('  C.OPSIYONGUN, CAST(ROUND(ISNULL((Select SUM(TUTARI) FROM CARIDOKUM');
  FSQLS.Add('    WHERE ISLEMTIPI IN(2, 24) AND CAST(TARIH AS INT) >= CAST(GETDATE() - ');
  FSQLS.Add('     (SELECT OPSIYONGUN FROM TBLCARI WITH(INDEX=TBLCARI_KARTNO) WHERE KARTNO = T.CARIID) AS INT)');
  FSQLS.Add('      AND CARIID = T.CARIID), 0), 2) AS DECIMAL(18, 2)) AS VADESIGELMEYENSATIS,');  
  FSQLS.Add('  T.SATIS, T.ODEME, T.BORC, T.VADE, CAST(GETDATE() - T.VADE AS INT) AS GUN');
  FSQLS.Add('from @TempTable T');
  FSQLS.Add('LEFT OUTER JOIN TBLCARI C WITH(INDEX=TBLCARI_KARTNO) ON C.KARTNO = T.CARIID');
  FSQLS.Add('LEFT OUTER JOIN TBLPERSONEL P ON P.KARTNO = C.SORUMLUPERSONEL');
  FSQLS.Add(') AS A');
  FSQLS.Add('WHERE');
//  FSQLS.Add('GUN > 0');
  FSQLS.Add(' KOD1 NOT IN(''g'', ''G'', ''s'', ''S'')');
 
//   FSQLS.SaveToFile('D:\Var.SQL');
   
  CreateReport(FSQLS.Text,FSQLS.Text, 
     'FIRMAKODU;FIRMAADI;BAKIYE;POSTAKODU:0;SEHIR:0;ULKE:=;PERSONEL;KOD1;KOD2:0;KOD3:0;KOD4:0;KOD5:0;SATIS;ODEME;BORC;VADESIGELMEYENSATIS;VADESIGECENSATIS;VADE;OPSIYONGUN;GUN', 
	 'Firma Kodu;Firma Adı;Bakiye;Posta Kodu;Şehir;Ülke;Personel;Ö.Kod 1;Ö.Kod 2;Ö.Kod 3;Ö.Kod 4;Ö.Kod 5;Satış;Ödeme;Borç;Vadesi Gelmeyen Satış;Vadesi Geçen Borç;Vade;Opsiyon;Gün', 
	  0);
  CreateSummary('SATIS', skSum); 
  CreateSummary('ODEME', skSum); 
  CreateSummary('BORC', skSum); 
  CreateSummary('VADESIGELMEYENSATIS', skSum); 
  CreateSummary('VADESIGECENSATIS', skSum); 
  CreateSummary('BAKIYE', skSum); 
End;

Begin
End.