Mail merge from based on grid records and exiting report or letter

[ruudluiten]

Hi,

I am trying to find a solution for the following.

- One of my customers prints collection reports on paper which are then send to the corresponding addresses (by postal mail). The report is generated based on a grid and using the CurrentGridFilter(). Only one PDF file is created, and printed, which is not a problem since the addresses appear in the letterhead.
- They want to send the collection reports by e-mail now so there is a need to generate PDF reports for each record in the grid since the PDF file will be attached.
- I can not find out how to create multiple pdf files (one for each record in the grid) and send each of the pdf file to the corresponding e-mail address in the record.

Any solution?

[[email protected]]

Re: Mail merge from based on grid records and exiting report or letter

Hi Ruud,

I have two different processes which combined may get you what you need.
1. A ux where a customer can e-mail invoices to his clients. He selects a range of invoice numbers and presses a button to send.
Invoices that are not send can be printed with a button 'no email address'.
The pdf's are already created upfront so it is just a matter of attaching the existing file to the email.

2. I have a grid to create an excel with prices etc for selected contracts.
Based on the selected contracts the actual prices are calculated and all the information is written to an excel file.

So if you have unique identifiers in your grid this could work for you.

Down there is some code from the grid.

Pieter


'create a sql arguments object
dim args as sql::arguments
'We read the arguments from e._gridCurrentArgumentsXML
args.xml = e._gridCurrentArgumentsXML

'create the sqlquery from a5_sql_addfilterorder(e.tmpl.cs.sql,e._gridCurrentFilter,"",e._gridCurrentOrder)
dim strsql as c
strsql = <<%sql%
___e.rtc.sql_final___;
%sql%
dim strQuery as c
strQuery = a5_sql_addfilterorder(e.tmpl.cs.sql,e._gridCurrentFilter,"",e._gridCurrentOrder)
strsql = STRTRAN( strsql, "___e.rtc.sql_final___" , strQuery )

'dim strres as c
'strres =args.xml + crlf() + strsql+ crlf() + e._gridCurrentFilter+crlf()+e.tmpl.cs.sql+crlf()+strQuery
'filename = "c:\temp\stroutput10ss.txt"
'file_pointer = file.create(filename, FILE_RW_EXCLUSIVE)
'file_pointer.write(strres)
'file_pointer.flush()
'file_pointer.close()

flag = cn.execute(strsql, args)
if flag = .f. then
'there was an error - close the connection and exit
cn.Close()
dim msg as c
msg = "Could not execute query. Error reported was: " + cn.CallResult.text + " 1query "+ strsql
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
Units_and_tenants= jscmd
exit function
end if
'The primary keys of the contracts that are selected are in this result
' so we will use this to calculate the actual amounts per unit.
'but first we have to get the reference date
'Check if there is a reference date if not then today
dim strvar as c
dim f1 as l
strvar = e._gridCurrentArgumentsXML
'test if whatstartdate is in the string
f1 = "whatstartdate" $ strvar
dim str__kntrruim__ as c 'variabel to only select units for peildatum
str__kntrruim__ = " and (kr.KNTRRUIM_eind_datum is null or kr.KNTRRUIM_eind_datum > :whatstartdate ) and (kr.KNTRRUIM_begin_datum <= :whatstartdate)"

if f1 = .f. then
args.set("whatstartdate",date())
if variable_exists("session.__protected__lic_CustomerCode") then
if session.__protected__lic_CustomerCode = "BIM" .or. session.__protected__lic_CustomerCode = "CPTXX" then
str__kntrruim__ = ""
end if
end if
end if



dim rs as sql::ResultSet
rs = cn.ResultSet
flag = rs.nextRow()
if flag = .f. then
'no rows found
cn.Close()
dim msg as c
msg = "<a5:r>Geen gegevens om te tonen</a5:r>"
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
Units_and_tenants= jscmd
exit function

end if

strsql = <<%sql%
CALL `procedure_K_contractruimte_actuele_prijzen`(:whatkontrakt_nummer, :whatstartdate);
%sql%
dim strkontrakt as c 'variabele for getting the in condition for the kontraktnumbers
while flag
strkontrakt = strkontrakt + convert_type(rs.data("kontrakt_nummer"),"C")
args.set("whatkontrakt_nummer",rs.data("kontrakt_nummer"))
flag = cn.execute(strsql, args)
if flag = .f. then
'there was an error - close the connection and exit
cn.Close()
dim msg as c
msg = "Could not execute procedure. Error reported was: " + cn.CallResult.text+" Contract: "+convert_type(rs.data("kontrakt_nummer"),"C")
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
Units_and_tenants= jscmd
exit function
end if
'when there are no more records in the ResultSet, executing .nextRow()
'will return .f., so flag will be .fl and the while loop will end
flag = rs.nextRow()
if flag = .t. then
strKontrakt = strkontrakt+", "
end if
end while

args.set("whatkontraktin",strKontrakt)

'strres =strkontrakt
'filename = "c:\temp\strkontrakt.txt"
'file_pointer = file.create(filename, FILE_RW_EXCLUSIVE)
'file_pointer.write(strres)
'file_pointer.flush()
'file_pointer.close()


'We have filled the database with the prices and now we can get the data to download.
'strsql = STRTRAN( strsql, "___e.rtc.sql_final___" , strQuery )
'Selected fields depends on customer
dim strselect as c
'strselect = "ko.KONTRAKT_nummer as Contract, ko.centra_kode as Pand, ce.Centra_omschrijving as Pandomschrijving, re.relaties_bedrijfs_naam as Huurder, kr.RUIMTEN_kode as Ruimte"
'two versions one for element and one for the rest

strselect = <<%txt%
:whatstartdate as Peildatum
, ko.centra_kode as Pand
, ce.Centra_omschrijving as Pandomschrijving
, coalesce(ad.Debiteur,'') as Debiteur
, re.relaties_bedrijfs_naam as Huurder
, ru.Ruimten_Straat as Ruimte_straat
, ru.Ruimten_huisnummer as Ruimte_huisnummer
, ru.Ruimten_Plaats as Ruimte_plaats
, kr.RUIMTEN_kode as Ruimte
, ru.Ruimten_omschrijving as Ruimte_omschrijving
, bk.BEST_KOD_omschrijving as Ruimte_bestemming
, ru.Ruimten_gebruiks_opp as Ruimte_gebroppervlakte
, ru.Ruimten_kontrakt_opp as Ruimte_kntroppervlakte
, ko.KONTRAKT_nummer as Contract

, coalesce(kr.KNTRRUIM_Contract_opp,0) as Ruimte_oppervlakte
, coalesce(kontraktruimte_aktueleprijzen.korting,0) as Korting
, coalesce(kontraktruimte_aktueleprijzen.huur,0) as Huur_per_maand
, coalesce(kontraktruimte_aktueleprijzen.servrest,0) as Servrest
, coalesce(kontraktruimte_aktueleprijzen.energie,0) as Energie
, coalesce(kontraktruimte_aktueleprijzen.verzekering,0) as verzekering
, coalesce(kontraktruimte_aktueleprijzen.promotie,0) as Promotie
, coalesce(kontraktruimte_aktueleprijzen.service,0) as Service_per_maand
, ko.Kontrakt_index_dat as IndexDatum
, kr.KNTRRUIM_begin_datum as Ruimte_begindatum
, ko.KONTRAKT_opzeg_dat as Opzegdatum
, kr.KNTRRUIM_eind_datum as Ruimte_Einddatum
, ko.KONTRAKT_opgezegd_dat as Beeindigd
, coalesce(ko.Optiekod_kode,'') as Verleng_optie
, ko.KONTRAKT_Betaalwijze as betaalwijze
, ko.kontrakt_betaal_periode as betaalperiode
, coalesce(ko.STAT_KOD_kode,'') as Status_contract
, coalesce(ko.KONTRAKT_opmerkingen,'') as Opmerkingen
, rs.RUIM_SRT_omschrijving as ruimtesoort

, coalesce(12 * kontraktruimte_aktueleprijzen.huur,0) as Huur_per_jaar
, coalesce(kontraktruimte_aktueleprijzen.periode_huur,0) as PeriodeHuur
, coalesce(kontraktruimte_aktueleprijzen.periode_service,0) as PeriodeService
, coalesce(kontraktruimte_aktueleprijzen.Periode_btw,0) as PeriodeBTW
, coalesce(kontraktruimte_aktueleprijzen.Periode_totaal,0) as Periodetotaal
, coalesce(kr.best_kod_kode,'') as Ruimte_bestemming_kode
, ko.RELATIES_nummer as Relaties_nummer
, coalesce(ko.ADMIN_kode,'') as Administratie
, ko.kontrakt_begin_dat as Begindatum
, ko.KONTRAKT_eind_dat as Einddatum
, ko.KONTRAKT_verlengd_tot as Verlengd_tot
, coalesce(ko.KONTRAKT_Faktureren,'') as Factureren
, if(ko.kontrakt_jaarperiode is null,if(ko.kontrakt_betaal_periode,1,if(ko.kontrakt_betaal_periode = 3,1,ko.kontrakt_betaal_periode)),ko.kontrakt_betaal_periode) as BetaalMaand
, coalesce(ko.KONTRAKTFactuurlayout,'') as Factuurlayout
, coalesce(ko.KONTRAKTBijlagen,'') as Bijlagen
, coalesce(ko.KONTRAKT_bestemming,'') as Bestemming
, coalesce(ko.KONTRAKT_oms_diensten,'') as Diensten
, if(ko.KontraktFactuurNietMailen = 'X','','X') as FactuurMailen
, if(ko.KontraktFactuurEmailRelat = 'X', re.Relaties_mail,ko.KontraktFactuurEmail) as Factuur_Mail_adres
, coalesce(re.relaties_telefoon,'') as Telefoon
, coalesce(re.relaties_fax,'') as Fax
, coalesce(re.relaties_straat_postbus,'') as Straat
, coalesce(re.Relaties_huis_postbus_num,'') as Huisnummer
, coalesce(re.Relaties_woonplaats,'') as Woonplaats
, coalesce(re.Relaties_postkode,'') as Postkode
, coalesce(re.Relaties_prive_persoon,'') as Privepersoon
, coalesce(re.RelatiesIBAN,'') as Bank_iban
, coalesce(re.RelatiesIbanGiro,'') as Giro_Iban
, coalesce(re.Relaties_zoekcode,'') as Zoekcode
, coalesce(re.Relaties_kvk_nummer,'') as Kvk_nummer
, coalesce(re.Relaties_doorschakelen,'') as Code
, coalesce(re.Relaties_adm_nummer,'') as Adm_code
, coalesce(re.Relaties_kvk_plaats,'') as Kvk_Plaats
, coalesce(re.Relaties_kvk_inschr_dat,'') as Kvk_datum
, coalesce(re.Relaties_mail,'') as Email
, coalesce(Relaties_creditcard,'') as Creditcard
, coalesce(re.Relaties_btw_nummer,'') as BtwNummer
, coalesce(re.Relaties_Mobiel,'') as Mobiel
, coalesce(re.Relaties_www,'') as Www
, kp.Kontpers_volg_nummer as Kontpnummer
, kp.Kontpers_default as Kontpdefault
, kp.Kontpers_achternaam as Achternaam
, kp.Kontpers_voorletters as Voorletters
, kp.Kontpers_tussenvoegsel as Tussenvoegsel
, kp.Kontpers_geslacht as Geslacht
, kp.Titu_kod_titulatuur as Titulatuur
, kp.Aans_kod_aanspreking as Aanspreking
, kp.Kontpers_roepnaam as Roepnaam
, kp2.Kontpers_volg_nummer as Kontpnummer2
, kp2.Kontpers_default as Kontpdefault2
, kp2.Kontpers_achternaam as Achternaam2
, kp2.Kontpers_voorletters as Voorletters2
, kp2.Kontpers_tussenvoegsel AS Tussenvoegsel2
, kp2.Kontpers_geslacht as Geslacht2
, kp2.Titu_kod_titulatuur as Titulatuur2
, kp2.Aans_kod_aanspreking as Aanspreking2
, kp2.Kontpers_roepnaam as Roepnaam2
, kp2.Kontpers_tweede as Kontptweede2
,`Function_P_strtav`(kp.kontpers_volg_nummer, kp2.kontpers_volg_nummer) as Tav
, `Function_P_RelatieBriefaanhef`(kp.kontpers_volg_nummer, kp2.kontpers_volg_nummer) as Aanhef
, ce.Centra_kntr_opp_totaal as Pand_kntroppervlakte
, ce.Centra_gebr_opp_totaal as Pand_gebroppervlakte
, coalesce(ko.KONTRAKT_jaar_termijn,0) as opzegtermijn_jaar, coalesce(ko.kontrakt_maand_termijn,0) as opzegtermijn_maand
, coalesce(ko.KONTRAKT_dagen_termijn,0) as opzegtermijn_dagen

%txt%
strquery = <<%sql%
select distinct ___select___

from kontrakt as ko join kntrruim as kr on ko.kontrakt_nummer = kr.kontrakt_nummer

__kntrruim__


join relaties as re on ko.relaties_nummer = re.relaties_nummer
left join kontpers as kp on re.relaties_nummer = kp.relaties_nummer and kp.kontpers_default = 'X'
left join kontpers as kp2 on re.relaties_nummer = kp2.relaties_nummer and kp2.kontpers_tweede = 'X'
left join admidebi as ad on ko.relaties_nummer = ad.relaties_nummer and ko.admin_kode = ad.admin_kode
join centra as ce on ko.centra_kode = ce.centra_kode
join ruimten as ru on kr.ruimten_kode = ru.ruimten_kode and kr.centra_kode=ru.centra_kode
left join kontraktruimte_aktueleprijzen on kontraktruimte_aktueleprijzen.KONTRAKT_nummer = ko.KONTRAKT_nummer
and kontraktruimte_aktueleprijzen.CENTRA_kode = ko.CENTRA_kode and kontraktruimte_aktueleprijzen.RUIMTEN_kode = kr.RUIMTEN_kode
left join best_kod as bk on bk.BEST_KOD_kode = ru.BEST_KOD_kode
left join ruim_srt as rs on rs.CENTRA_kode = ru.CENTRA_kode and rs.RUIM_SRT_kode = ru.RUIM_SRT_kode

WHERE KO.KONTRAKT_NUMMER in (___e.rtc.sql_final___)

order by ko.centra_kode, re.relaties_bedrijfs_naam, ko.relaties_nummer, ko.kontrakt_nummer, kr.ruimten_kode ;
%sql%
strsql = STRTRAN( strquery, "___e.rtc.sql_final___" , strkontrakt )
strsql = STRTRAN( strsql, "___select___" , strselect )
strsql = STRTRAN( strsql, "__kntrruim__" , str__kntrruim__ )

'Resultaat wegschrijven naar een txt bestand
'filename = "c:\temp\output.txt"
'file_pointer = file.create(filename, FILE_RW_EXCLUSIVE)
'file_pointer.write("query: "+strsql+crlf()+" final: "+strkontrakt)
'file_pointer.flush()
'file_pointer.close()


flag = cn.execute(strsql, args)

if flag = .f. then
'there was an error - close the connection and exit
cn.Close()
dim msg as c
msg = "Could not get existing records for kontraktin. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
Units_and_tenants= jscmd
exit function
end if

dim liAantalRecords as n
dim flagp as l
rs = cn.ResultSet

flagp = rs.nextrow()
'check if there is anything to display
if flagp = .f. then
'there where no records
liAantalRecords = 0
cn.Close()
dim msg as c
msg = "<a5:r>Geen gegevens om te tonen</a5:r>."
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
Units_and_tenants= jscmd
exit function
end if
dim strFilename as c = Request.GetRequestTempFileName("xls")
flag = rs.toExcel(strFilename)
if flag = .f. then
'there was an error - close the connection and exit
cn.Close()
dim msg as c
msg = "Could not write data to xls file. Error reported was: " + strFilename
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
Units_and_tenants= jscmd
exit function
end if
cn.close()
dim strFilenameuser as c
strFilenameuser = "Units_And_Tenants.xls"
if file.exists(strfilename)
Session.SaveDataAsFile(strFilenameuser,file.to_blob(strfilename))
jscmd = "window.open('"+Session.FormatFileDataURL(strFilenameuser)+"');"
end if
'jscmd = jscmd+"alert('<a5:r>Gereed</a5:r>.');"
Units_and_tenants= jscmd