1:  * This program looks up all the Rev data from Document and populates a table called REV which is stored in SYS(2023)
   2:  * 
   3:  * It also populates the prior used Rev Var's in case a program requires them.
   4:  *
   5:  * Date: 5/28/2008 
   6:  * By: Doug Symes
   7:  *
   8:  LPARAMETERS lcRevMemo AS Character, lcRevMemo5Col AS Character, lcRevMemo6Col AS Character, lcPlacardRevMemo AS Character
   9:   
  10:  PUBLIC cSeek, nCount, lIntelex, cSQL, nIntelexMultRevConn
  11:   
  12:  nIntelexMultRevConn = 0
  13:  cSQL = ''
  14:  nCount = 0
  15:  cSpec_cont =""
  16:  cSeek = ""
  17:   
  18:  lcRevMemo = ''  && this is fed to the Work Traveler or whatever needs a concatenated version of all the revs.
  19:  lcRevMemo5Col = ''
  20:  lcRevMemo6Col = ''
  21:  lcPlacardRevMemo = ''  && for printing in a much smaller area.
  22:   
  23:  ** assumes these var's are declared by the calling app
  24:  Draw_rev=""
  25:  Alpl_rev=""
  26:  Trav_rev=""
  27:  Spec_rev=""
  28:   
  29:  ******* Use Intelex for revision data if lIntelex = .T. ********
  30:  lIntelex=.F.  
  31:  IF !USED("intelex_vs_VFPDoc") Then
  32:     USE Admin!intelex_vs_VFPDoc IN 0 SHARED
  33:  ENDIF
  34:  SELECT intelex_vs_VFPDoc
  35:  GO top
  36:  IF RECCOUNT() =1 Then
  37:     IF intelex Then
  38:        lIntelex =.T.
  39:     ENDIF
  40:  ENDIF
  41:  IF USED("Intelex_vs_VfpDoc") Then
  42:     SELECT Intelex_vs_VfpDoc
  43:     USE
  44:  ENDIF
  45:  ****************************************************************
  46:   
  47:  IF lIntelex Then
  48:  *   MESSAGEBOX("Pulling Revision data from Intelex.",0+64,2000)
  49:      OPEN DATABASE Intelex SHARED 
  50:   
  51:      nIntelexMultRevConn = SQLCONNECT("IW_SQL")
  52:   
  53:      
  54:      *****************************************************************************************************************
  55:  ***    ********************************** Get all possible Types from Intelex ******************************************
  56:  ***    cSQL = "SELECT Description AS Type FROM dcDocumentTypes ORDER BY Type"
  57:  ***    nQry = SQLEXEC(nIntelexMultRevConn,cSQL,'DocTypes')
  58:  ***    SELECT ALLTRIM(harness) as item, type, '        ' as rev FROM DocTypes INTO TABLE SYS(2023)+"\tblAllTypes"
  59:      
  60:      *****************************************************************************************************************
  61:      *** See if the Spec Cont field in the Part Library is different than the StockCode given and if so capture it ***
  62:      SELECT part_no, spec_cont from part_li WHERE part_li.part_no==harness INTO CURSOR spec_chk
  63:      IF part_no<>spec_cont Then
  64:         cSpec_Cont = ALLTRIM(spec_chk.spec_cont)
  65:      ENDIF
  66:          
  67:      ****** Find all exact matches ******
  68:      cSQL = "SELECT D.Documentnumber as Doc, DT.Description as Type, DR.RevisionNumber as Rev"+;
  69:             "  FROM dcDocuments D "+;
  70:             " INNER JOIN dcDocumentTypes DT ON D.TypeID=DT.TypeID "+;
  71:                " INNER JOIN dcDocumentRevisions DR ON D.DocumentID = DR.DocumentID AND D.ReleasedRevID = DR.RevisionID "+;
  72:             " WHERE D.DocumentNumber = '"+ALLTRIM(harness)+"'"+;
  73:             "   AND D.DocumentType = 'A' AND D.DocumentStatus = 'A'"+;
  74:             " GROUP BY Documentnumber , Description , RevisionNumber "
  75:      nQry = SQLEXEC(nIntelexMultRevConn,cSQL,'Doc1')
  76:   
  77:      SELECT doc, type, rev FROM Doc1 INTO TABLE SYS(2023)+"\tblRev"
  78:   
  79:      ALTER TABLE tblRev alter COLUMN doc c(20)
  80:      ALTER TABLE tblRev alter COLUMN type c(4)
  81:      INDEX ON doc+type TAG doc_type
  82:      INDEX ON doc TAG document
  83:      INDEX ON type TAG tag
  84:      INDEX ON type+doc TAG type_doc
  85:   
  86:      SELECT doc1
  87:      USE    
  88:      cSQL = "SELECT D.Documentnumber as Doc, DT.Description as Type, DR.RevisionNumber as Rev"+;
  89:             "  FROM dcDocuments D "+;
  90:             " INNER JOIN dcDocumentTypes DT ON D.TypeID=DT.TypeID "+;
  91:                " INNER JOIN dcDocumentRevisions DR ON D.DocumentID = DR.DocumentID AND D.ReleasedRevID = DR.RevisionID "+;
  92:             " WHERE D.DocumentNumber = '"+ALLTRIM(cSpec_Cont)+"'"+;
  93:             "   AND D.DocumentType = 'A' AND D.DocumentStatus = 'A'"+;
  94:             " GROUP BY Documentnumber , Description , RevisionNumber "+;
  95:             " ORDER BY Doc, Type, Rev DESC"
  96:      nQry = SQLEXEC(nIntelexMultRevConn,cSQL,'Doc1')
  97:      IF RECCOUNT() > 0 Then
  98:         SELECT doc1
  99:         SCAN
 100:            SELECT tblRev
 101:            LOCATE FOR ALLTRIM(tblrev.type) == ALLTRIM(doc1.type) 
 102:               IF !FOUND() then
 103:               INSERT INTO tblRev (doc, type, rev) VALUES (ALLTRIM(doc1.doc),ALLTRIM(doc1.Type),ALLTRIM(doc1.rev))
 104:            ENDIF
 105:         ENDSCAN
 106:      ENDIF
 107:       
 108:      *** for comparison to Intelex 
 109:      SELECT document.* ;
 110:        FROM Config!document ;
 111:       WHERE ALLTRIM(document.doc)==harness AND !Retired;
 112:        INTO CURSOR curDocCompare
 113:   
 114:  ** list of doc+types not in Intelex but in Document System
 115:      SELECT curdoccompare.doc, curdoccompare.type ;
 116:        FROM curdoccompare ;
 117:       WHERE curdoccompare.doc+curdoccompare.type NOT in (select tblrev.doc+tblrev.type FROM tblrev) INTO CURSOR NOCURSOR
 118:   
 119:      IF RECCOUNT()>0 Then
 120:  **                  "On the following window you will see a list of them."+CHR(13)+CHR(13)+;
 121:         MESSAGEBOX("There are Types in the Document System missing from Intelex."+CHR(13)+CHR(13)+;
 122:                    "Close the window to continue.",0+16,"Warning!")
 123:  *       SELECT NOCURSOR
 124:  *       BROWSE NODELETE NOEDIT TITLE "Close the window to continue..." 
 125:         SELECT NOCURSOR
 126:         USE
 127:      ENDIF
 128:      SELECT tblRev    
 129:  ELSE
 130:     IF !USED("Document") Then
 131:        USE Config!Document IN 0 SHARED 
 132:     ENDIF
 133:     
 134:      SELECT document.* ;
 135:        FROM Config!document ;
 136:       WHERE ALLTRIM(document.doc)==harness AND !Retired;
 137:        INTO CURSOR curDoc
 138:   
 139:      SELECT curDoc.doc, curDoc.Rev, Doc_Type.Type ;
 140:        FROM curDoc RIGHT OUTER JOIN Config!doc_type ;
 141:          ON curDoc.type==doc_type.type ;
 142:       ORDER BY doc_type.type ;
 143:        INTO TABLE SYS(2023)+"\tblRev"
 144:   
 145:      REPLACE ALL Doc WITH harness
 146:   
 147:      ** Any cases of a Spec Control showing a different Part_no?
 148:      SELECT part_li.part_no, part_li.spec_cont ;
 149:        FROM Config!Part_li ;
 150:       WHERE Part_no == harness ;
 151:        INTO CURSOR curSpec
 152:      SELECT curSpec
 153:      LOCATE FOR !(part_no==spec_cont)
 154:      IF FOUND() Then
 155:         cSpec_cont = ALLTRIM(curSpec.Spec_Cont)
 156:         SELECT tblRev
 157:         SCAN FOR ISNULL(tblRev.Rev)
 158:           SELECT Document
 159:           LOCATE FOR !RETIRED AND ALLTRIM(Doc) == ALLTRIM(curSpec.Spec_Cont) AND document.type == tblrev.type 
 160:           IF FOUND() Then
 161:              REPLACE tblRev.Rev WITH Document.Rev
 162:           ENDIF
 163:         ENDSCAN
 164:      ENDIF
 165:   
 166:      SELECT tblRev
 167:      DELETE ALL FOR ISNULL(tblRev.Rev)
 168:      PACK
 169:      INDEX ON doc+type TAG doc_type
 170:   
 171:  ENDIF
 172:   
 173:  *************************************************
 174:  ** Fill old Variables just in case.
 175:  *************************************************
 176:   
 177:  * ALPL rev
 178:  cSeek = PADR(ALLT(Harness), LEN(tblRev.doc))+"ALPL"
 179:  SEEK cSeek
 180:  IF FOUND() THEN
 181:     Alpl_rev=tblRev.Rev
 182:  ELSE && No records in Part_li either.
 183:     ALPL_rev=""
 184:  ENDIF
 185:   
 186:  * WORK TRAVELER rev
 187:  cSeek = PADR(ALLTRIM(Harness),LEN(tblRev.doc))+"TRAV"
 188:  SEEK cSeek
 189:  IF FOUND() THEN
 190:      Trav_rev=tblRev.Rev
 191:  ELSE
 192:      Trav_rev=""
 193:  ENDIF
 194:   
 195:  * DRAW rev
 196:  cSeek = PADR(ALLT(Harness), LEN(tblRev.doc))+"DRAW"
 197:  SEEK cSeek
 198:  IF FOUND() Then
 199:      DRAW_rev=tblRev.Rev
 200:  ELSE && But if not, then try to get the rev via part_li
 201:      DRAW_rev=""
 202:  ENDIF
 203:   
 204:  * SPEC rev
 205:  cSeek = PADR(ALLTRIM(Harness),LEN(tblRev.doc))
 206:  SEEK cSeek
 207:  IF ALLTRIM(Type)=="SPEC"  && Find SPEC
 208:     SPEC_rev=tblRev.Rev
 209:  ELSE && No records in Part_li either.
 210:      SPEC_rev=""
 211:  ENDIF
 212:   
 213:  IF USED("curDoc") then
 214:     SELECT curDoc
 215:     USE
 216:  ENDIF
 217:  IF USED("Doc_Type") then
 218:     SELECT Doc_Type
 219:     USE
 220:  ENDIF
 221:   
 222:  ********************************************************************************************
 223:  nCount = 0
 224:  lcRevMemo = ''
 225:  SELECT tblRev
 226:  SCAN
 227:     nCount = nCount + 1
 228:     lcRevMemo = lcRevMemo + PADR(tblRev.type+": "+ALLTRIM(tblRev.rev),16)
 229:     If nCount = 4 Then
 230:        lcRevMemo = lcRevMemo + CHR(13)+CHR(10)         && use var instead of file
 231:        nCount = 0
 232:     ENDIF
 233:  ENDSCAN
 234:  ********************************************************************************************
 235:  ********************************************************************************************
 236:  nCount = 0
 237:  lcRevMemo5Col = ''
 238:  SELECT tblRev
 239:  SCAN
 240:     nCount = nCount + 1
 241:     lcRevMemo5Col = lcRevMemo5Col + PADR(tblRev.type+": "+ALLTRIM(tblRev.rev),16)
 242:     If nCount = 5 Then
 243:        lcRevMemo5Col = lcRevMemo5Col + CHR(13)+CHR(10)         && use var instead of file
 244:        nCount = 0
 245:     ENDIF
 246:  ENDSCAN
 247:  ********************************************************************************************
 248:  ********************************************************************************************
 249:  nCount = 0
 250:  lcRevMemo6Col = ''
 251:  SELECT tblRev
 252:  SCAN
 253:     nCount = nCount + 1
 254:     lcRevMemo6Col = lcRevMemo6Col + PADR(tblRev.type+": "+ALLTRIM(tblRev.rev),16)
 255:     If nCount = 6 Then
 256:        lcRevMemo6Col = lcRevMemo6Col + CHR(13)+CHR(10)         && use var instead of file
 257:        nCount = 0
 258:     ENDIF
 259:  ENDSCAN
 260:  ****************************************************************************************************************************************************************************************
 261:  * For use with the Placard report
 262:  ********************************************************************************************
 263:  nCount = 0
 264:  lcPlacardRevMemo= ''
 265:  SELECT tblRev
 266:  SCAN
 267:     nCount = nCount + 1
 268:     lcPlacardRevMemo= lcPlacardRevMemo+ PADR(tblRev.type+":"+ALLTRIM(tblRev.rev),14)
 269:     If nCount = 3 Then
 270:        lcPlacardRevMemo= lcPlacardRevMemo+ CHR(13)+CHR(10)         && use var instead of file
 271:        nCount = 0
 272:     ENDIF
 273:  ENDSCAN
 274:  ********************************************************************************************
 275:  IF USED('curDocCompare') Then
 276:     SELECT curDocCompare
 277:     USE
 278:  ENDIF
 279:  IF USED('tblRev') then
 280:     SELECT tblRev
 281:     USE
 282:  ENDIF
 283:  DELETE FILES SYS(2023)+"\tblRev.dbf"
 284:  DELETE FILES SYS(2023)+"\tblRev.cdx"
 285:  SQLDISCONNECT(nIntelexMultRevConn)
 286:   
 287:  *MESSAGEBOX(lcrevmemo,0+64,"lcRevMemo")
 288:  *IF TYPE("lcRevMemo5Col")='C' Then
 289:  *   MESSAGEBOX("Exiting Intelex_Multi_Rev: "+CHR(13)+lcRevMemo5Col,0+64,"lcRevMemo5Col")
 290:  *ENDIF