ALTER PROCEDURE "dba"."dk_automeeldek"( )
BEGIN
  declare ld_perlop date;
  declare ld_kuup date;
  declare ll_asutnr integer;
  declare ll_skoop integer;
  declare ldc_summa decimal(15,2);
  declare ls_mess long varchar;
  declare ls_asutnr1 char(101);
  declare ls_address char(50);
  declare err_notfound exception for sqlstate value '02000';
  declare c_koond dynamic scroll cursor for
    select asutnr,get_asulist_nimi_quick(asutnr),ldeeb,kuup from gl_dk_koond;
  set ld_perlop=Today();
  set ls_address='info@pmen.ee';
  set ll_skoop=1;
-- arvuta koond
  call dk_koond_meelde_nimek(ll_skoop,0,ld_perlop);
-- koosta tekst
  open c_koond;
  koond_loop: loop
    fetch next c_koond into ll_asutnr,ls_asutnr1,ldc_summa,ld_kuup;
    if sqlstate = err_notfound then -- kui eof
      leave koond_loop
    end if;

    set ls_mess='<!DOCTYPE html>';
    set ls_mess=ls_mess || '<html style="margin: 0;padding: 0;width: 100%;">';
    set ls_mess=ls_mess || '<head>';
    set ls_mess=ls_mess || '<title>Meeldetuletus</title>';
    set ls_mess=ls_mess || '</head>';
    set ls_mess=ls_mess || '<body style="margin: 0;padding: 0;width: 100%;">';
    set ls_mess=ls_mess || '<p>Hea klient ' + ls_asutnr1 + '!</p>';
    set ls_mess=ls_mess || '<p>Käesolevaga teatame, et meie raamatupidamise andmetel on Teie võlg meile seisuga ' + CONVERT(char(10),ld_perlop,104) + '.a. ' + CONVERT(char(20),ldc_summa) + ' eurot</p>';
    set ls_mess=ls_mess || '</body>';
    set ls_mess=ls_mess || '</html>';

-- lisa meilidesse
    call mail_add_filelist(7,ld_kuup,ll_asutnr,left(ls_asutnr1,100),ls_address,'Meeldetuletus',ls_mess,1,ll_skoop);

-- saada meil
--    call saadameilid(ls_mess);
--    leave koond_loop;
  end loop koond_loop;
  close c_koond;
END