*  Program...........: Etv2Sca.PRG
*) Description.......: Add a customer record from the ETV online address
*)                   : database into an accounting system (Btrieve 
format).
*)                   : The address is copied into the clipboard. Each 
field
*)                   : is separated with a semikolon.
*)                   : This program performs the following steps:
*)                   : 
*)                   :  - parse the clipboard and fill the array 
laEtvClip.
*)                   :    Each array element corresponds to a field in 
EtvTel
*)                   :  - check whether the selected customer already 
exists
*)                   :    in Scala or not. If yes, the user is informed 
and the
*)                   :    program is cancelled
*)                   :  - get the next customer key from the table 
CustKey
*)                   :  - insert the record into Scala
*)                   :     - check whether the update was successful or 
not and
*)                   :    inform user accordingly
*)                   :  - store the customer key into the clipboard
*)                   :    
*--------------------------------------------------------- 
---------------------
PRIVATE lnFields, i, lnStart, lnEnd, lnTotalLen

WAIT WINDOW NOWAIT "Preparing data import..."

#DEFINE ccDelimiter ";"

*-- determine number of fields
lnFields = OCCURS(ccDelimiter, _CLIPTEXT)

*-- make sure clipboard contains an address (5 fields)
IF lnFields <> 6
    =MESSAGEBOX("No ETV Address in the clipboard. Please select an 
address in ETV and copy it to the clipboard." , 48, "Scala Customer 
Import")
    RETURN -1
ENDIF

PRIVATE laEtvClip

DIMENSION laEtvClip[lnFields]

lnStart = 1
lnTotalLen = LEN(ALLTRIM(_CLIPTEXT))

#DEFINE cnLastName 1
#DEFINE cnFirstName 2
#DEFINE cnStreet 3
#DEFINE cnPLZ 4
#DEFINE cnCity 5
#DEFINE cnTel 6

*-- parse clipboard and fill array laEtvClip
FOR i = 1 TO lnFields
    lnEnd = ATC(ccDelimiter, _CLIPTEXT, i)
    laEtvClip[i] = SUBSTR(_CLIPTEXT, lnStart, lnEnd-lnStart)
    lnStart = lnEnd+1
ENDFOR &&* i = 1 TO lnFields

PRIVATE jcSelect
jcSelect = SELECT()

*-- open remote view
OPEN DATABASE Etv.dbc SHARED
USE sl01 NOREQUERY

*-- very slow!

*!*    *-- make sure address doesn't exist
*!*    WAIT WINDOW NOWAIT "Checking for duplicate customer..."

*!*    *-- make sure address doesn't exist
*!*    LOCATE FOR CHRTRAN(laEtvClip[cnTel],"/\*()-+ ","") = 
CHRTRAN(telephone_number,"/\*()-+ ","")

*!*    IF FOUND()
*!*        *-- record already exists. Inform user and cancel program
*!*        =MESSAGEBOX("Customer "+ALLTRIM(laEtvClip[cnLastName])+" 
"+laEtvClip[cnFirstName]+" already in Scala!"+CHR(13)+;
*!*                    "Please check Customer No 
'"+ALLTRIM(sl01.customer_code)+"'!" , 48, "Scala Customer Import")
*!*        _CLIPTEXT = ""
*!*        SELECT (jcSelect)
*!*        RETURN -1
*!*    ENDIF

*-- get next customer no from table CustKey 
--------------------------------

IF USED("CustKey")
    SELECT CustKey
ELSE
    SELECT 0
    USE CustKey SHARED
ENDIF

*-- attempt to lock record for jnTryLock-seconds
PRIVATE jnAttemptLock, jnTryLock, jcCustKey

jnAttemptLock = SECONDS()
jnTryLock     = 5           && we try for 5 seconds

DO WHILE NOT RLOCK()
    WAIT WINDOW TIMEOUT 1 "Waiting for locked record..."
    *-- exit if we couldn't get the lock within jnTryLock
    IF jnAttemptLock + jnTryLock < SECONDS()
        EXIT
    ENDIF
ENDDO

IF RLOCK()
    *-- we've got the lock, so we proceed
    *-- get key value for next record
    jcCustKey = PADR(ALLTRIM(CustKey.cNextKey), 10)
    *-- increment key field by 1
    REPLACE CustKey.cNextKey WITH STR(VAL(CustKey.cNextKey)+1)
    *-- remove lock
    UNLOCK
    *--finally, we add the customer to Scala
    INSERT INTO sl01 (customer_code    ,;
                      customer_name    ,;
                      alpha_search_key ,;
                      address_line_1   ,;
                      address_line_4   ,;
                      zip_code         ,;
                      telephone_number) ;
                     VALUES(jcCustKey  ,;
                            CPCONVERT(1252, 850, 
ALLTRIM(laEtvClip[cnLastName])+" "+laEtvClip[cnFirstName]),;
                            UPPER(CPCONVERT(1252, 850, 
ALLTRIM(laEtvClip[cnLastName])+" "+laEtvClip[cnFirstName])),;
                            CPCONVERT(1252, 850, laEtvClip[cnStreet]),;
                            CPCONVERT(1252, 850, 
ALLTRIM(laEtvClip[cnPLZ])+" - "+laEtvClip[cnCity]),;
                            CPCONVERT(1252, 850, 
ALLTRIM(laEtvClip[cnPLZ])),;
                            CPCONVERT(1252, 850, 
STRTRAN(laEtvClip[cnTel],"*","")))

    *-- check whether update was successful or not and inform user 
appropriately                           
    SELECT sl01
    IF TABLEUPDATE()
        *-- insert successful
        =MESSAGEBOX("Customer "+ALLTRIM(laEtvClip[cnLastName])+" 
"+laEtvClip[cnFirstName]+" added to Scala."+CHR(13)+;
                    "Customer No = '"+jcCustKey + "'", 64, "Scala 
Customer Import")

        *-- store customer key into clipboard
        _CLIPTEXT = jcCustKey
    ELSE
        *-- insert failed
        *-- undo insert
        =TABLEREVERT()
        *-- inform user
        =MESSAGEBOX("An error occurred. Customer 
"+ALLTRIM(laEtvClip[cnLastName])+" "+laEtvClip[cnFirstName]+" not added 
to Scala. Please contact support.", 48, "Scala Customer Import")
    ENDIF

ELSE
    *-- lock failed, so we inform user
    =MESSAGEBOX("Cannot generate customer key because record is locked by
another user. Try again later.", 48, "Scala Customer Import")
ENDIF

SELECT (jcSelect)
RETURN 1
*--------------------------------------------------------- 
---------------------