FiveTech Software tech support forums

Hello Everyone,

I am having a problem getting exact match response from MS SQL. I have provide code below, can someone tell me what I am doing wrong?

//-----------------------------------------------------------------------------
function _AesRead( oSay )

local lEof := .F. , ;
cLine := "" , ;
cFolder := "" , ;
cOrg := "" , ;
cDst := "" , ;
cTmp := "" , ;
nTmp := 0 , ;
n001 := 0 , ;
c001 := "" , ;
n001H := 0 , ;
nE := 0 , ;
nW := 0 , ;
nX := 0 , ;
nX0 := 0 , ;
nX7 := 0 , ;
nX8 := 0 , ;
nX9 := 0 , ;
nY := 0 , ;
nZ := 0 , ;
nMax := 100 , ;
nW1 := 0 , ;
cShipno := "" , ;
cTaxid := "" , ;
cResponse := "" , ;
cAesitn := "" , ;
cTxdate := "" , ;
cTxtime := "" , ;
aAesmsg := {} , ;
cSourceF := "" , ;
cHistory := "" , ;
cRespcode := "" , ;
cDispcode := "" , ;
cSeverity := "" , ;
aAceaes := {} , ;
cSqlins := "" , ;
cSqlseek := "" , ;
oSqlIns , ;
oSqlSeek

DO CASE
CASE Pdebug="TEST"
cSourceF := "c:\winapps\aceaes\inbox\"
cHistory := "c:\winapps\aceaes\inbox\history\"
CASE Pdebug="LIVE"
cSourceF := "\\cdm-mq\ace-aes\inbox\"
cHistory := "g:\aceaes\"
ENDCASE

TRY
oSqlIns:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait( "Unable to load ACE AES SQL Record Set", Ptitle)
RETURN (.F.)
END
oSqlIns:CursorType := 1 // opendkeyset
oSqlIns:CursorLocation := 3 // local cache
oSqlIns:LockType := 3 // lock opportunistic

TRY
oSqlSeek:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait( "Unable to load ACE AES SQL Record Set", Ptitle)
RETURN (.F.)
END
oSqlSeek:CursorType := 1 // opendkeyset
oSqlSeek:CursorLocation := 3 // local cache
oSqlSeek:LockType := 3 // lock opportunistic

// Init...
xSQL := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD

// Message..
oSay:SetText( "Checking for CBP ACE AES Response Messages . . ." )
Sysrefresh()

// USCS...
cMask := "*.*"
cFolder := cSourceF
aAceaes := Directory( cFolder + cMask )

// Convert and Process...
FOR nX9=1 TO LEN(aAceaes)

// Message...
oSay:SetText( "Processing CBP Responses: " + LTRIM(STR(nX9,9)) + "/" + LTRIM(STR(LEN(aAceaes),9)) + ", " + LTRIM( STR( ROUND( (nX9/LEN(aAceaes))*100, 2), 9, 2 ) ) + "% Complete..." )
SysRefresh()

// Init...
lEof := .F.
c001 := aAceaes[nX9,1]
n001H := FOPEN( cFolder + c001, 0)
cShipno := ""
cTaxid := ""
cResponse := ""
cAesitn := ""
cTxdate := ""
cTxtime := ""
aAesmsg := {}
cRespcode := ""
cDispcode := ""
cSeverity := ""
lError := .F.
aEmail := {}
cEmResp := ""

// Error...
IF n001H <> -1 // Read-Only...

// Process...
DO WHILE ! lEof

// Refresh...
SysRefresh()

// Read...
cLine := Ureadln( n001H, 1, @lEof )

// Trim...
cLine := ALLTRIM( cLine )

// Update...
IF ( ! EMPTY( cLine ) )
DO CASE
CASE LEFT(cLine,1)=="A"
cTaxid := SUBSTR( cLine, 6, 9 )
cTxdate := SUBSTR( cLine,24, 8 )
cTxtime := TIME()

CASE LEFT(cLine,3)=="SC1"
cShipno := SUBSTR( cLine, 15, 17 )

CASE LEFT(cLine,3)=="ES1"
cRespcode := SUBSTR( cLine, 4, 3 )
cDispcode := SUBSTR( cLine, 8, 1 )
cSeverity := SUBSTR( cLine, 9, 1 )
cResponse := SUBSTR( cLine,11,40 )
cEmResp += cResponse + cEol
cAesitn := SUBSTR( cLine,51,15 )
AADD( aAesmsg, { cShipno, cTaxid, cTxdate, cTxtime, cRespcode, cDispcode, cSeverity, cResponse, cAesitn } )
ENDCASE
ENDIF
ENDDO

// Close...
FCLOSE( n001H )

// Get E-mail...
cTxemail := ""
IF (! EMPTY(cTaxid)) .AND. (! EMPTY(cShipno))
cSqlSeek := "SELECT * FROM AESTRX WHERE ein = '" + ALLTRIM(cTaxid) + "' AND shipmentno = '" + ALLTRIM(cShipno) + "' ORDER BY shipmentno DESC"
TRY
oSqlSeek:Open( cSqlSeek, xSQL )
CATCH oError
lError := .T.
END
IF (oSqlSeek:RecordCount>0)
cTxemail := oSqlSeek:Fields( "TXEMAIL" ):Value
ENDIF
ENDIF
IF VALTYPE(cTxemail)<>"C"
cTxemail := "aceaes@cdmsoft.com"
ENDIF

// Init...
cSqlIns := ""

FOR nX8=1 TO LEN(aAesmsg)
IF nX8=1
cSqlins := "INSERT INTO AESRESPONSE (shipmentno,taxid,txdate,txtime,respcode,dispcode,severity,response,aesitn,txemail) VALUES "
ENDIF
cSqlIns += "("
cSqlIns += "'" + aAesmsg[nX8,1] + "',"
cSqlIns += "'" + aAesmsg[nX8,2] + "',"
cSqlIns += "'" + aAesmsg[nX8,3] + "',"
cSqlIns += "'" + aAesmsg[nX8,4] + "',"
cSqlIns += "'" + aAesmsg[nX8,5] + "',"
cSqlIns += "'" + aAesmsg[nX8,6] + "',"
cSqlIns += "'" + aAesmsg[nX8,7] + "',"
cSqlIns += "'" + aAesmsg[nX8,8] + "',"
cSqlIns += "'" + aAesmsg[nX8,9] + "',"
cSqlIns += "'" + cTxemail + "')"
IF nX8=LEN(aAesmsg)
cSqlIns += ";"
ELSE
cSqlIns += ","
ENDIF
NEXT nX8
IF ! EMPTY( cSqlIns )
TRY
oSqlIns:Open( cSqlIns, xSQL )
CATCH oError
lError := .T.
END
ENDIF

// Copy to history...
IF ! EMPTY(cTxemail)

// Init...
cTemp := ""
aEmail := {}

FOR nX7=1 TO LEN(cTxemail)
cChar := SUBSTR( cTxemail, nX7, 1 )
IF cChar==","
IF ! EMPTY( cTemp )
AADD( aEmail, cTemp )
ENDIF
cTemp := ""
ELSE
cTemp += cChar
ENDIF
NEXT nX7
IF ! EMPTY(cTemp)
AADD( aEmail, cTemp )
ENDIF

// Init...
cMsgFile := ""
nMsgFile := 1

DO WHILE (.T.)
cMsgFile := Pdrive + 'ACE' + DTOS(DATE()) + RIGHT( "00000" + LTRIM(STR(nMsgFile,9)), 9 ) + ".AES"
IF FILE(cMsgFile)
nMsgFile++
LOOP
ENDIF
EXIT
ENDDO

hHan := FCREATE( cMsgFile, 0 )
IF (hHan <> -1)
cDat := "SHIPMENT REFERENCE NUMBER : " + ALLTRIM( cShipno ) + cEol
FWRITE( hHan, cDat )
IF ! EMPTY( cAesItn )
cDat := "AES ITN : " + ALLTRIM( cAesItn ) + cEol
FWRITE( hHan, cDat )
ENDIF
cDat := "* * * ACE AES RESPONSE DETAILS * * *" + cEol
FWRITE( hHan, cDat )
cDat := "====================================" + cEol
FWRITE( hHan, cDat )
cDat := cEmResp
FWRITE( hHan, cDat )
FCLOSE( hHan )

// Init...
cSubject := "CDM ACE AES Response: " + ALLTRIM(cShipno)
IF EMPTY(cAesItn)
cSubject += " - Rejected"
ELSE
cSubject += " - Accepted"
ENDIF
cEmTo := ALLTRIM( aEmail[01] )
cEmCC := ""
IF LEN(aEmail)>1
cEmCC := "<" + ALLTRIM( aEmail[02] ) + ">"
ENDIF

// Mailer...
cMailer := cEmTo // 01 - To
cMailer += ' '
cMailer += CHR(34) + ALLTRIM( cSubject ) + CHR(34) // 02 - Subject
cMailer += ' '
cMailer += cMsgFile // 03 - E-mail body
cMailer += ' '
cMailer += cEmCC // 04 - CC

// XPP Run...
cExec = Pdrive + "mailer.exe"
WaitRun( cExec + ' ' + cMailer )
ENDIF

// Copy to History...
COPY FILE( cFolder + c001 ) TO (cHistory + c001 )
IF FILE( cHistory + c001 )
FERASE( cFolder + c001 )
ENDIF
ENDIF
ENDIF

NEXT nX9

// Message...
oSay:SetText( "CDM U.S. Customs Response Complete!" )
Sysrefresh()

return (0)