>I told my boss I would try but don't really know how. But I bet someone here can help.
>
>We have a phone switch that outputs a data stream to a particular port. Through TCO/IP and HyperTerminal this data can be viewed. I need to capture this data stream and write it to a table.

I recently completed a project where I had to Poll some Time Entry stations over our LAN, retrieve their stored records of the ID cards that had been scanned through them and post those records to a MySQL database.

I used VFP 9.0 with .NetExtender to access the TCPClient in the .Net Framework 2.0.

The code ended up simpler than I expected. See the following code for how simple it was.

*
** Read and Log Data From Savetime Clocks
*
CLEAR
ON ESCAPE Quit
DO WHILE .T.
SET ESCAPE OFF 
DO GetDatabaseConnection
** Get List of Clocks
LOCAL lcQuery
lcQuery = ""
TEXT TO lcQuery TEXTMERGE NOSHOW 
	SELECT ID,Description,IPAddress,IPPort,TerminalID,Active
	 FROM cardreaders.cardreaders
	 WHERE Active = "Y" AND LEFT(Description,11) = "Front Gate "
	 ORDER BY IPAddress ASC;
ENDTEXT 
SQLEXEC(_Screen.Connection,lcQuery)
SQLIDLEDISCONNECT(_Screen.Connection)
** Process each Clock
SELECT SQLResult
SCAN 
DO ReadAndLogClock WITH IPAddress,IPPOrt,TerminalID,ID
ENDSCAN 
SQLDISCONNECT(_Screen.Connection)
SET ESCAPE ON 
WAIT "Press 'Esc' to terminate" TIMEOUT 300
ENDDO 
*
** Read and Log Clock
*
PROCEDURE ReadAndLogClock
LPARAMETERS ClockIP,ClockPort,ClockTerminalID,ID
#DEFINE EOT 0x04
#DEFINE ETX 0x03
#DEFINE ACK 0x06
#DEFINE SOH 0x01
#DEFINE STX 0x02
#DEFINE GS 0x1d
#DEFINE RS 0x1e
#DEFINE NAK 0x15
LOCAL oClock, ClockData,lcQuery,pString,cardnumber,timestamp
	oClock = CREATEOBJECT('CardReader')
	IF	oClock.Connect(m.ClockIP,m.ClockPort)
	
**		Get Clock data
		ClockData = ""
		DO WHILE .T.
			ClockData = PollClock(oClock,ClockTerminalID)
			IF ClockData == CHR(ETX)
				EXIT 
			ELSE 
**				Insert record into Front Gate In-Out file
				IF SUBSTR(Clockdata,9,2) = "BP"
					pString = SUBSTR(ClockData,11)
					DO CASE 
						CASE LEFT(pString,3) == "031"
							inout = "In"
						CASE LEFT(pString,3) == "032"
							inout = "Out"
						OTHERWISE 
							inout = "   "
					ENDCASE 		
					pString = SUBSTR(pString,6)
					cardnumber = ALLTRIM(LEFT(pString,AT(CHR(RS),pString)-1))
					cardnumber = RIGHT("00000000"+cardnumber,8)
					timestamp = RIGHT(pString,15)
					timestamp = "20"+LEFT(timestamp,2)+"-"+SUBSTR(timestamp,3,2)+"-"+SUBSTR(timestamp,5,2)+" "+SUBSTR(timestamp,7,2)+":"+SUBSTR(timestamp,9,2)+":"+SUBSTR(timestamp,11,2)
					lcQuery = ""
					TEXT TO lcQuery TEXTMERGE NOSHOW 
						INSERT INTO cardreaders.frontgateinout
						(CardNumber,TimeStamp,Direction,CardReaderID,CardData)
						Values
						("<<cardnumber>>","<<timestamp>>","<<inout>>","<<ID>>","<<EscapeThisString(ClockData)>>")
						;
					ENDTEXT 
					SQLEXEC(_screen.Connection,lcQuery)
				ENDIF 
			ENDIF 
		ENDDO 
		SQLIDLEDISCONNECT(_Screen.Connection)	
			
**		Reset Clock's Date and Time
		= ResetClockDateTime(oClock,ClockTerminalID	)
		
**		Disconnect from Clock
		oClock.DisConnect
	ELSE 
	ENDIF 
RETURN 


*
** Poll Clock
*
PROCEDURE PollClock
LPARAMETERS oClock, ClockTerminalID
LOCAL cText,ClockData
**	Send Poll
	oClock.Write(CHR(EOT)+ClockTerminalID+"P"+CHR(ETX))
**	Get Response
	cText = oClock.Read()
	IF cText == CHR(ETX)
		ClockData = cText
	ELSE 
		ClockData = cText
		IF LRCCheck(ClockData)
**			Send ACK
			oClock.Write(CHR(ACK)+CHR(ETX))
**			Wait for ACK of ACK
			cText = oClock.Read()	
			IF cText == CHR(ETX)
			ELSE 
			ENDIF 
		ELSE 
**			Send NAK
			oClock.Write(CHR(NAK))
			ClockData = CHR(ETX)
		ENDIF 
	ENDIF 
RETURN ClockData


*
** Reset Clock's Date and Time
*
PROCEDURE ResetClockDateTime
LPARAMETERS oClock, ClockTerminalID
LOCAL cText

**	Select Clock
	oClock.Write(CHR(EOT)+ClockTerminalID+"S"+CHR(ETX))

**	Get Response
	cText = oClock.Read()	

**	IF ACK
	IF cText == CHR(ACK)+CHR(ETX)

**		Send Set Time Command
		cText = CHR(SOH)+ClockTerminalID+CHR(STX)+"\J"+RIGHT(ClockTerminalID,1)+CHR(GS)+RIGHT(TTOC(DATETIME(),1),12)+CHR(RS)
		cText = cText+LRCCalculate(cText)+CHR(ETX)
		oClock.Write(cText)

**		Get Response
		cText = oClock.Read()	

**		IF ACK
		IF cText == CHR(ACK)+CHR(ETX)

**			Send End-of-Transaction
			oClock.Write(CHR(ETX))
		ELSE 
			RETURN .F.
		ENDIF 
	ELSE 
		RETURN .F.
	ENDIF 
RETURN .T.


*
** LRC Calculate
*
PROCEDURE LRCCalculate
LPARAMETERS LRCSection
LOCAL LRC,I
	LRC = ASC(SUBSTR(LRCSection,1,1))
	FOR I = 2 TO LEN(LRCSection)
		LRC = BITXOR(LRC,ASC(SUBSTR(LRCSection,I,1)))
	NEXT I
	LRC = BITOR(LRC,0x40)
RETURN CHR(LRC)


*
** LRC Check
*
PROCEDURE LRCCheck
LPARAMETERS ClockData
RETURN SUBSTR(ClockData,LEN(Clockdata)-1,1) = LRCCalculate(SUBSTR(ClockData,1,LEN(ClockData)-2))


*
** Card Reader Class
*
DEFINE CLASS CardReader as Custom 
	PROTECTED oClock,oClockStream,oByteArray,oTextConverter

**	Make TCP Connection to CardReader
	FUNCTION Connect
	LPARAMETERS ClockIP,ClockPort
	LOCAL Connected
		This.oClock = CLRCREATEOBJECT("System::Net::Sockets::TcpClient")
		TRY 
			This.oClock.Connect(ClockIP,ClockPort)
			Connected = .T.
		CATCH 
			Connected = .F.	
		FINALLY 
		ENDTRY 
		IF Connected
			? "Connected to CLock:"+ClockIP+":"+STR(ClockPort)
			This.oClockStream = This.oClock.GetStream()
			This.oByteArray = CLRCreateZeroBasedArray("System::Byte",256)
			This.oTextConverter = CLRCreateObject("System::Text::ASCIIEncoding")
		ELSE 
			? "Connection to clock:"+ClockIP+":"+STR(ClockPort)+ " Failed!"
		ENDIF 
	RETURN Connected
	ENDFUNC 

**	Write to Card Reader
	FUNCTION Write
	LPARAMETERS cText
	LOCAL ByteArrayLength
		ByteArrayLength = This.oTextConverter.GetBytes(cText,0,LEN(cText),This.oByteArray,0)
		This.oClockStream.Write(This.oByteArray,0,ByteArrayLength)
		? cText
	ENDFUNC 
	
**	Read from Card Reader
	FUNCTION Read
	LOCAL ByteArrayLength,cText
		ByteArrayLength = This.oClockStream.Read(This.oByteArray,0,This.oByteArray.Length)
		cText = This.oTextConverter.GetString(This.oByteArray,0,ByteArrayLength)
		? SPACE(4),cText
		RETURN cText
	ENDFUNC 
	
**	Disconnect from CardRaeder
	FUNCTION DisConnect
		This.oClockStream.Close()
		This.oClock.Close()
		? "Disconnected from Clock"
	ENDFUNC 	
ENDDEFINE