PC Toledo

Código: Selecionar todos

SEEK cUF + cCidade

Código: Selecionar todos

PROCEDURE Main2

   LOCAL cnAccess, aDbList, cDb

   Set( _SET_CODEPAGE, "PTISO" )
   cnAccess := ConexaoAccess()
   cnAccess:Open()
   aDbList := ListFiles( cnAccess )
   FOR EACH cDb IN aDbList
      ListRecords( cnAccess, cDb )
   NEXT
   ConsultaMeuCep( cnAccess )

   cnAccess:Close()

   RETURN

FUNCTION ConexaoAccess()

   LOCAL cnAccess

   cnAccess   := win_OleCreateObject( "ADODB.Connection" )
   cnAccess:ConnectionString := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files (x86)\correios\Access\dbgpbe.mdb;Mode=Share Deny Write;Persist Security Info=False;Jet OLEDB:Database Password=«¹½¿¥Ð²þÞ¢§³¼»"

   RETURN cnAccess

Código: Selecionar todos

#define ADSCHEMATABLES 20

FUNCTION ListFiles( cnAccess )

   LOCAL Rs, aDbList := {}, cTb

   Rs := cnAccess:OpenSchema( ADSCHEMATABLES )
   DO WHILE ! Rs:Eof()
      cTb := rs:fields( "TABLE_NAME" ):Value
      IF Left( cTb, 3 ) == "LOG"
         AAdd( aDbList, cTb )
      ENDIF
      Rs:MoveNext()
   ENDDO
   Rs:Close()

   RETURN aDbList

Código: Selecionar todos

FUNCTION ListRecords( cnAccess, cDb )

   LOCAL Rs, nCont

   ? Pad( cDb, 20, "-" )
   Rs := cnAccess:Execute( "SELECT TOP 1 * FROM " + cDb )
   FOR nCont = 0 TO Rs:Fields:Count() - 1
      ? Rs:Fields( nCont ):Name, Rs:Fields( nCont ):Value
   NEXT
   Rs:Close()

   RETURN NIL

Código: Selecionar todos

FUNCTION ConverteCEP( cCodigo )

   LOCAL nCont, cTxt, cNovo := "", cNum

   FOR nCont = 1 TO Len( cCodigo ) STEP 2
      cTxt := Substr( cCodigo, nCont, 2 )
      DO CASE
      CASE cTxt $ "UG,LA,AL,GU" ; cNum := "0"
      CASE cTxt $ "X8,8X,CN,NC" ; cNum := "1"
      CASE cTxt $ "Z0,EP,PE,0Z" ; cNum := "2"
      CASE cTxt $ "GR,B1,1B,RG" ; cNum := "3"
      CASE cTxt $ "3D,DI,D3,ID" ; cNum := "4"
      CASE cTxt $ "SJ,C4,4C,JS" ; cNum := "5"
      CASE cTxt $ "2A,A2,QH,HQ" ; cNum := "6"
      CASE cTxt $ "FO,09,90,OF" ; cNum := "7"
      CASE cTxt $ "7Y,DM,Y7,MD" ; cNum := "8"
      CASE cTxt $ "5V,BK,V5,KB" ; cNum := "9"
      OTHERWISE                 ; cNum := "X"
      ENDCASE
      cNovo += cNum
   NEXT

   IF Len( cCodigo ) != 16 // não é cep, não converte
      cNovo = cCodigo
   ENDIF

   RETURN cNovo

Código: Selecionar todos

PROCEDURE Main

   LOCAL cnMySql, cTable, aTable, aConvertList, cSql, Rs, cNumCep, nID, nKey, cCampo, cChave, nCont, nTotal

   Set( _SET_CODEPAGE, "PTISO" )
   cnMySql := ConexaoMySql()
   cnMySql:Open()

   aConvertList := { ;
      { "LOG_UNID_OPER",      "UOP_KEY_DNE", "UOP_NU_SEQUENCIAL" }, ;
      { "LOG_LOGRADOURO",     "LOG_KEY_DNE", "LOG_NU_SEQUENCIAL" }, ;
      { "LOG_LOCALIDADE",     "LOC_KEY_DNE", "LOC_NU_SEQUENCIAL" }, ;
      { "LOG_GRANDE_USUARIO", "GRU_KEY_DNE", "GRU_NU_SEQUENCIAL" }, ;
      { "LOG_CPC",            "CPC_KEY_DNE", "CPC_NU_SEQUENCIAL" } }

   FOR EACH aTable IN aConvertList
      cTable := aTable[ 1 ]
      cCampo := aTable[ 2 ]
      cChave := aTable[ 3 ]
      ? "tabela", cTable, "campo", cCampo, "chave", cChave
      Rs := cnMySql:Execute( "SELECT COUNT(*) AS QTD FROM " + cTable )
      nTotal := Rs:Fields( "QTD" ):Value
      Rs:Close()
      cSql := "SELECT NUMCEP, " + cChave + ", " + cCampo + " FROM " + cTable
      Rs := cnMySql:Execute( cSql )
      nCont := 0
      DO WHILE nKey != K_ESC .AND. ! Rs:Eof()
         nKey := Inkey()
         nCont++
         cNumCep := ConverteCep( Rs:Fields( cCampo ):Value )
         nID     := Rs:Fields( cChave ):Value
         IF Mod( nCont, 1000 ) = 0
            ? cTable, nTotal, nCont, Rs:Fields( cChave ):Value, nID, cNumCep
         ENDIF
         IF Rs:Fields( "NUMCEP" ):Value == NIL .OR. Len( Trim( rs:Fields( "NUMCEP" ):Value ) ) = 0
            cSql := "UPDATE " + cTable + " SET NUMCEP='" + cNumCep + "' WHERE " + cChave + "=" + Ltrim( Str( nID ) )
            cnMySql:Execute( cSql )
         ENDIF
         Rs:MoveNext()
      ENDDO
      Rs:Close()
   NEXT
   cnMySql:Close()

   RETURN