/* class MailProcess This class validates rows, mails info to a recipient based on "this.cMailField", marks the row as "sent" and moves on looking for another. If it doesn't find any, it sleeps for a few seconds, then does a reQuery() on the query to see if anything new has come in. This is a database-polling mail class. It uses SEE32.dll from MarshallSoft for connection and sending via SMTP protocol. Your eMail server and sender eMail address must be specified in the UI Derivation This class was derived from a more specific class developed for Carik Services Inc. in Denver Co. Author: A. A. Katz V.2.0 2/1/2000 Copyright: 1998-2000 A. A. Katz */ Class MailProcess /////// Method: Set up query /////////////////////////////////////// /////// Purpose: initializes repeated requeries //////////////////// /////// Params: oForm - requires a form reference be passed /////// oQuery - reference to query created in calling form /////// rangeLookup - optional lookup value for setRange() /////// The alternative option is to define the query's /////// SQL property to find only open, "unsent" rows. /////// MarkSent - data to insert into field to mark it as /////// "sent" /////// Note: Marksent can take any kind of data, but it will /////// substitute Date() if you send the string "Today" function init(oForm,oQuery,MarkSent) ///// This query must be designed to find "new" unsent rows, either ///// by setrange() into an indexed field this.form = oForm this.oQuery = oQuery this.markSent = iif(empty(MarkSent),"",MarkSent) if type('This.MarkSent') = 'C' and ; upper(this.MarkSent) = 'TODAY' this.MarkSent = upper(this.Marksent) endif this.cUpdatefield = "" this.cPrimaryField = "" this.cMailField = "" ///// Insurance for missing primary fielename this.cPrimaryField = 1 ////// Method: SetPrimaryFieldName()////////////////////////////// ////// Purpose: identifies field name to use as primary key for /// ////// reporting errors ////////////////////////////////// function setPrimaryFieldName(cFieldName) this.cPrimaryField = cFieldName ////// Method: SetUpdateFieldName()////////////////////////////// ////// Purpose: identifies field name to use to replace with /// ////// markSent so mail does not do infinite loop ////// function setUpdateFieldName(cFieldName) if empty(cFieldName) msgbox('Update field name required!') return false endif this.cUpdateField = cFieldName ////// Method: SetMailFieldName()////////////////////////////// ////// Purpose: identifies field name to use to retrieve mail ////// address /// function setMailFieldName(cFieldName) if empty(cFieldName) msgbox('Mail address field name required!') return false endif this.cMailField = cFieldName ////// Method: SetSubject)////////////////////////////////////// ////// Purpose: sets text to be used as the Subject line in ////// eMail Messages ////////////////////////////////// function setSubject(cSubjectText) if empty(cSubjectText) msgbox('Subject text required!') return false endif this.cSubject = cSubjectText ////// Method: ProcessData //////////////////////////////////////// /////// Purpose: Looks for table rows to be sent via e-Mail /////// /////// Finds first row, processes next, next, till there /////// are none, then sleeps for n second interval and tries /////// again. function ProcessData() // send query ///// init host and email address setups cHost = trim(this.form.notebook1.hostfield.value) cFromAddress = '<'+lower(trim(this.form.notebook1.AddressField.value))+'>' if not this.Form.logError('Started by user') return false endif ///// outer loop checks, waits, checks agtain ///// to see if there are ANY new unsent messages in the query ///// reQuery() is probably required to refresh the rowset ///// with new data added from Web Site or LAN. Do while true if this.form.Cancel() return false endif ////// This counter displays hours/minutes since last message sent nIdleTime = seconds() - this.form.nLastActivity cHours = str(int(nIdleTime/3600),2) cMinutes = ltrim(str(int(nIdleTime%360/60),2)) cSeconds = ltrim(str(int(nIdleTime%60),2)) this.form.notebook1.statusbar.text = 'Nothing new for '+cHours+' hours '+; cMinutes+' minutes '+; cSeconds+' seconds' Try this.oQuery.rowset.refresh() catch(exception e) ////// Could not requery table!!! Return log error this.form.LogError('Database Access Error: '+e.message) this.form.notebook1.statusbar.text = 'Idling... waiting for database connection' sleep(20) this.form.resetDisplay(this.form.notebook1.checking) loop endTry ///// turn the "checking" display red this.form.resetDisplay(this.form.notebook1.checking) ///// Cancel button pressed? if this.form.Cancel() return false endif /////// if there's not any pending rows to send ///////////////////////// bFound = this.oQuery.rowset.FindKey(NULL) //// if you find an empty index key if not bFound ///// kill some time and if not cancel button pressed,start process ///// over again if not this.killtime() return false endif loop endif if this.form.cancel() return false endif ////// If there are pending messages, connect to Mail Server this.form.notebook1.statusbar.text = 'Connecting to mail server...' this.form.resetDisplay(this.form.notebook1.connecting) ////// Call the connect method to connect to SMTP Mail Server if not this.connect() // cancel pressed (not connect()) seeClose(1) return false endif ////// Processing Loop ////////////////////////////////////// ////// ////////////////////////////////////// this.form.notebook1.statusbar.text = "Validating Data...." this.form.resetDisplay(this.form.notebook1.validating) ///// Keep going as long as value is null do while this.oQuery.rowset.findkey(null) ////// test for cancel button if this.form.cancel() seeClose(1) return false endif //////////// Validation Section /////////////////////////////// ///////////// /////////////////////////////// ////// attempt to validate row if not class::validate() /////// if lock fails, log error and go to next row this.form.LogError('Error: Unable to validate: '+; this.oQuery.rowset.fields[this.cPrimaryField].value) //// stuff a temporary in the update field class::getRidOfNull() loop endif ////// If there is no eMail address on file if empty(this.oQuery.rowset.fields[this.cMailField].value) /////// if lock fails, log error and go to next row this.form.LogError('Error: No eMail address available for '+; this.oQuery.rowset.fields[this.cPrimaryField].value) class::getRidOfNull() if this.form.cancel() seeClose(1) return false endif loop endif ////// attempt to lock row if not this.oQuery.rowset.lockRow() /////// if lock fails, log error and go to next row this.form.LogError('Error: Unable to lock row to update: '+; this.oQuery.rowset.fields[this.cPrimaryField].value) class::GetRidOfNull() loop endif if this.form.cancel() seeClose(1) return false endif ////// Compose, build, address and send message x = this.sendMessage() if type('x') = 'C' and x = 'Cancel' // cancelbutton pressed seeClose(1) // disconnect return false elseif not x // Mail not sent loop endif // Mail HAS been sent, // continue on... ////////////// Final update, message has been sent /////// Mark sent and look for next one in order /////// if one not found, don't worry, this loop /////// will "requery()" if type('this.MarkSent') = 'C' and ; this.MarkSent = 'TODAY' this.MarkSent = ; iif( this.oQuery.rowset.fields[this.cUpdateField].type =; "CHAR", dtos(date()), date()) endif ////// Update the updateField value cPrimary = trim(this.oQuery.rowset.fields[this.cPrimaryField].value) this.oQuery.rowset.fields[this.cUpdateField].value = this.MarkSent this.oQuery.rowset.save() this.form.logError('Record updated: '+cPrimary) if this.form.cancel() seeClose(1) return false endif enddo ////// Do while not enfOfSet ///////////////// This is the disconnecting Section /////////////// ///////////////// /////////////// this.form.fLog.close() this.form.notebook1.statusbar.text = "Disconnecting from mail server..." this.form.resetDisplay(this.form.notebook1.disconnecting) this.form.nLastActivity = seconds() seeClose(1) ////// disconnect from server after all done this.form.resetDisplay(this.form.notebook1.checking) ////// go back and requery() and see if anything new has come in. enddo ////// outerloop return true ////// Method: mPuts() ////////////////////////////////////////// ////// Purpose: Concatenates strings together for eMaill message // ////// Params: cMessage - existing message string //////////////// ////// cNewLine - the new line you wish to add /////////// ////// Note that this takes strings meant to be individual ////// lines, adding CR/LF after each //////////////////// function mPuts(cMessage,cNewLine) if len(trim(cNewLine)) > 0 cMessage+= trim(cNewLine)+chr(13)+chr(10) endif return cMessage ////// Method: GenerateMailMessage() /////////////////////////////// ////// Use this method to -subclass- to generate the body of ////// your eMail message. This generic one just goes through ////// your fields and copies the data out. function GenerateMailMessage cMsg = "" private n for n = 1 to this.oQuery.rowset.fields.size cName = this.oQuery.rowset.fields[n].fieldName cValue = "" // forces auto type converstion to text cValue += this.oQuery.rowset.fields[n].value class::mPuts(cMsg,cName+': '+cValue) next return cMsg ////// Method: ValidateDate ///////////////////////////////////////////// ////// Purpose: Check for missing data, etc. before sending eMail /////// ////// Data should be validated before storing, so this ////// method should check stuff to do with mailing only. ////// Override this method in a subclass and add your ////// validation code to return true or false function Validate ////// roll your own return true ////// Method: GetRidOfNull() ///////////////////////////////////////// ////// Purpose: Kills off null by saving a default value /////////////// ////// takes a row out of index/findKey() range if it ///////// ////// fails validation function getRidOfNull cType = this.oQuery.Rowset.fields[this.cUpdateField].type newVal = null if cType = "CHAR" newVal = 'Error' elseif cType = 'DATE' newVal = { / / } elseif cType = 'NUM' or cType = 'LONG' or cType = 'DOUBLE' newVal = 0 endif if newVal # null this.oQuery.rowset.fields[this.cUpdateField].value = newval this.oQuery.rowset.save() endif return true ////// Method: Killtime() /////////////////////////////////////////////// ////// Purpose: Executes a sleep in a loop so that the cancel button ///// ////// will still be active, even during a "sleep(20)"! ///////// function KillTime ////// Wait for 15 seconds, checking cancel button for n = 1 to 40 if this.form.cancel() return false endif sleep(.25) next return true ////// Method: Connect() //////////////////////////////////////////////// ////// Purpose: Uses MarshallSoft Mail Lib to connect to Mail server ///// function Connect(nRetries) ////// Loop: Retry connection loop nNumRetries = iif(empty(nRetries),10,nRetries) nRetries = nNumRetries do while true if this.form.Cancel() return false endif ///// Try to connect cNull = "" ////// This is the key code for shareware version ////// when you register, change the second param ////// to match your key code seeAttach(1, 0) ////// Connect to Mail Host nError = seeSmtpConnect(0, cHost, cFromAddress, "") ////// if there's a connect error if nError < 0 cErrorBuff = space(80) seeErrorText(0,nError,cErrorBuff,81) ////// if connect error occurs, log error this.form.LogError('Comm Error: '+nError+' '+cErrorBuff) ////// and retry up to 10 times, then give error in log ////// and start all over again nRetries -- if nRetries <= 0 this.form.LogError('Connection Error: '+cErrorBuff) sleep(5) this.form.LogError('Retrying connection after 5 seconds') nRetries = nNumRetries endif if this.form.cancel() return false endif sleep(.5) if this.form.cancel() return false endif loop endif exit // connected OK enddo return true ////// Method: sendMessage() //////////////////////////////////////// ////// Purpose: Transmits the message via Marshallsoft SMTP library // function sendMessage this.form.notebook1.statusbar.text = 'Sending Mail to: '+; this.oQuery.rowset.fields[this.cMailField].value this.form.resetDisplay(this.form.notebook1.sending) ////// make sure vars have been configured this.cMailAddress = '<'+trim(this.oQuery.rowset.fields[this.cMailField].value)+'>' this.cMessage = class::GenerateMailMessage() nRetries = 10 do while true cError = space(80) ////// Attempt to send message n = seeSendEMail(0,this.cMailAddress,"","",this.cSubject,this.cMessage,"") nRetries -- ////// if it works if n >= 0 // no error this.form.logError('Mail Sent to: '+; trim(this.ntos(this.oQuery.rowset.fields[this.cPrimaryField].value))+; ' at '+this.ntos(this.cMailAddress)) return true endif if this.form.cancel() return "Cancel" endif ////// Get error message from MarshallSoft .DLL cError = space(80) seeErrorText(0,n,cError,81) if nRetries <= 0 // give up this.form.logError('Mail Error: '+cError+ ' Sending to: '+; ntos(this.oQuery.rowset.fields["this.cPrimaryField"].value)) class::getRidOfNull() return false endif exit if this.form.cancel() return "Cancel" endif enddo return true function nTOs(var) return iif(var = null,'',var) endclass