CGW/CGWB1300.PRG

* MIKE LEWIS  - UPDATE TABLES FROM GREAT PLAINS DBF - CGW1300 09-22-94

#INCLUDE 'RQB.CH'
#include "RASQLB.CH"
**  #INCLUDE 'F:\CLIP52\RASQLB\52\RQB.CH'
**  #include "F:\CLIP52\RASQLB\52\RASQLB.CH"
#INCLUDE 'INKEY.CH'

// CONSTANTS
#define A_CUSNO  2   // 2nd Element in CUSMAS Field Array (customer #)

EXTERNAL N_XVIA

*****************************************************
FUNCTION CGWB1300(MFILE, BFILE, BKEY_FLD, DO_MSG)
// UPDATE TABLE DBFs WITH GREAT PLAINS INFO

    // MFILE = CGW FILE NAME
    // BFILE = BTRIEVE FILE NAME
    // BKEY_FLD = AN ARRAY OF BTRIEVE KEY FIELDS NEEDE TO LOOKUP CGW RECORDS
    // DO_MSG = LOGICAL FLAG TO DISPLAY MESSAGE BELOW

LOCAL MSTRUCT, MFLD_NAMES
LOCAL FLDS_TO_GET := {}, GP_FLD, CGW_FLD, MVAL
LOCAL BARM_FACTOR, XXX, OPT, MWORK_AREA, ALL := .T.
LOCAL DO_IT := .F.

IF DO_MSG = NIL
  DO_MSG = .T.
ENDIF

IF DO_MSG
  CLS
  @ 10,15 SAY 'About to UPDATE the TABLE Files from the'
  @ 11,15 SAY 'Great Plains File. This process may take  '
  @ 12,15 SAY 'several minutes to complete.'
  OPT = ' '
  @ 14,20 SAY 'Do you wish to continue? (Y/N)' GET OPT PICTURE '!' VALID OPT$'YN'
  READ
  IF LASTKEY() = 27 .OR. OPT$'N'
    RETURN
  ENDIF
ENDIF

// GO OPEN AND SETUP BTRIEVR FILE
RESULT = SET_BTREV(BFILE, MFILE)
MTABLE = RESULT[1]
MSTRUCT = RESULT[2]


MWORK_AREA = N_XSELECT()

// OPEN UP CGW MASTER FILE
DBOPEN(MFILE)



FLDS_TO_GET = GPFLDLIST(BFILE)

IF DO_IT
  QBROWSE(2,2,20,79)
ENDIF


BARM_FACTOR = SETBARM(,, 20, N_XLASTREC() )

XXX = 0
N_XGOTOTOP()     // TOP OF BTRIVE FILE
DO WHILE !N_XEOF()
  XXX++
  BARM_UPDATE(,,XXX, BARM_FACTOR)

  IF MFILE = 'CUST_MAST'
    MCUSNO = N_XFETCH(A_CUSNO)  // GET CURRENT GREAT PLAINS CUSTOMER ID
    MKEY = N_XFETCH('BANK')  // GET CURRENT CUSTOMER ID
  ELSE
    MKEY = ''
    FOR L = 1 TO LEN(BKEY_FLD)
      XKEY = N_XFETCH(BKEY_FLD[L]) // GET CURRENT GREAT PLAINS KEY VALUE
      IF VALTYPE(XKEY) = 'N'
        MKEY = MKEY + LTRIM(STR(XKEY))
      ELSE
        IF VALTYPE(XKEY) = 'D'
          MKEY = MKEY + DTOS(XKEY)
        ELSE
          MKEY = MKEY + XKEY
        ENDIF
      ENDIF
    NEXT
  ENDIF

  // WATCH OUT FOR NULL CHARACTERS!!!
  FOR L2 = 1 TO LEN(MKEY)
    X = SUBSTR(MKEY,1,L2)
    IF ASC(X) = 0
      X = ' '
      MKEY = LEFT(MKEY,L2-1) + X + RIGHT(MKEY, LEN(MKEY)-L2)
    ENDIF
  NEXT

  SELECT &MFILE
  SEEK MKEY   // CGW CUST ID OR FILE KEY
  IF !FOUND()
    ADD_REC(1)
  ELSE
    REC_LOCK(1)
  ENDIF

  N_XSELECT(MWORK_AREA)  // SELECT BTRIEVE FILE

  FOR L = 1 TO LEN(FLDS_TO_GET)
    GP_FLD = FLDS_TO_GET[L,1]
    CGW_FLD = FLDS_TO_GET[L,2]



    IF MFILE = 'CUST_MAST'

      DO CASE

        CASE GP_FLD == 'PHONE'
          MVAL = SETDASH(GP_FLD)

        CASE GP_FLD == 'FAX'
          MVAL = SETDASH(GP_FLD)

        CASE GP_FLD == 'SHIPPHN'
          MVAL = SETDASH(GP_FLD)

        CASE GP_FLD == 'SHIPFAX'
          MVAL = SETDASH(GP_FLD)

        CASE GP_FLD == 'TERMS'
          MVAL = STR(N_XFETCH(GP_FLD),2)

        CASE GP_FLD == 'SHP_METHOD'
          MVAL = STR(N_XFETCH(GP_FLD),2)

        CASE GP_FLD == 'SLSMAN'
          MVAL = STR(VAL(N_XFETCH(GP_FLD)),5)

        CASE GP_FLD = 'SHIP'
          // ONLY GET SHIPPING INFO FOR CUSTOMERS WITH '01' OR '02'
          // IN THE LAST 2 DIGITS IN THE CUST ID
          IF RIGHT(MKEY,2) = '01' .OR. RIGHT(MKEY,2) = '02'
            MVAL = N_XFETCH(GP_FLD)
          ELSE
            MVAL = ''
          ENDIF

        OTHERWISE
          MVAL = N_XFETCH(GP_FLD)

      ENDCASE
    ELSE
      MVAL = N_XFETCH(GP_FLD)
    ENDIF

    REPLACE &MFILE->&CGW_FLD WITH MVAL
  NEXT


  N_XSKIP(1)
ENDDO

N_XCLOSE(ALL)

CLOSE DATABASES
RETURN







* * * * * * * * * * * * * * * * * * * * * * * *
FUNCTION SET_BTREV(MFILE, MDESC)
//  OPEN UP THE BTRIEVE FILE

CLS
WAIT_BOX(' *** UPDATING ' + MDESC + ' FILE ***  ',;
         ' ***      PLEASE WAIT       *** ')

N_XLOGIN()
N_XERRLVL(3)

MSTRUCT := {}
MFLD_NAMES := {}
SELECT 0

NET_USE('CGWBTREV', .F., 1, 'DEFINITIONS')   && CUSTOMER CHANGE FLAG
GO TOP
DO WHILE !EOF()
  IF TRIM(SYSNAME) == MFILE
    AADD(MSTRUCT, FNAME + TYPE + LENGTH + DECIMAL_PT + NUM_DECS + FILLER + SEMI_COLON)
    AADD(MFLD_NAMES, FNAME)
  ENDIF
  SKIP 1
ENDDO
USE


NET_USE('&CONTROL', .F., 5, 'CONTROL')   && CUSTOMER CHANGE FLAG
MPATH = ALLTRIM(CONTROL->BTR_PATH)
MTABLE = MPATH + MFILE + '.DAT'
USE

SET DEFAULT FILETYPE TO '.DAT'
SET RDD TO 'RQBRDD'

N_XSELECT(0)  // SELECT NEXT EMPTY BTRIEVE WORK AREA
N_XUSE(MTABLE, MSTRUCT)


SET DEFAULT FILETYPE TO
SET RDD TO

RETURN {MTABLE, MSTRUCT}

* * * * * * * * * * * * * * * * * *
FUNCTION SETDASH(MFIELD)
// PUT IN DASHES FOR THE PHONE NUMBER IN &MFILD

LOCAL MVAL

MVAL = TRIM(N_XFETCH(MFIELD))  // GET PHONE NUMBER
MVAL = LEFT(MVAL,3) + '-' + SUBSTR(MVAL,4,3) + '-' + RIGHT(MVAL,4)
RETURN MVAL