PC Toledo

Em homenagem a Paulo Cesar Toledo
https://pctoledo.org/

dbf x mysql

https://pctoledo.org/viewtopic.php?t=24792

Página 1 de 1

dbf x mysql

Enviado: 07 Out 2020 10:11
por pena
Alguém sabe de algum programa para converter dbf para mysql, que se possa selecionar o campo DE -> PARA

dbf x mysql

Enviado: 07 Out 2020 13:35
por Fernando queiroz
eu usei um programa feito pelo quintas e fui ajustando para as minhas necessidade

Código: Selecionar todos

/*********************************************
* compilar: hbmk2 demo.prg rddsql.hbc sddodbc.hbc hbwin.hbc
#define DBS_NAME        1
#define DBS_TYPE        2
#define DBS_LEN         3
#define DBS_DEC         4
*********************************************/


REQUEST DBFCDX	
REQUEST HB_CODEPAGE_PTISO, HB_CODEPAGE_PT850

#include "hbgtinfo.ch"
#include "directry.ch"
#include "dbstruct.ch"
#define MYSQL_MAX_CMDINSERT   500000

******************************************************************************************
FUNCTION MAIN( cServer, cDatabase, cUser, cPassword, nPort  )
******************************************************************************************

LOCAL oCatalog, oCn, cSql, cString, oFiles, oStru, cTable, nCont2, cSqlInsert, oFileDbf, nCont
LOCAL FORNECEDORES_ID:=2000;
	, oQuery1;
	, oQuery2;
	, oQuery3;
	, cQuery1;
	, cQuery2;

LOCAL lBegin := .T.

	RddSetDefault( "DBFCDX" )
	Set( _SET_CODEPAGE, "PTISO" )
	
	hb_Default( @cServer, "192.168.15.200" )
	hb_Default( @cDatabase, "DATABASENAME" )
	hb_Default( @cUser, "root" )
	hb_Default( @cPassword, "senha_do_root" )
	hb_Default( @nPort, "3306" )

	CLS
	SET DATE BRIT
	SET BELL OFF
	SET SCORE OFF
	SET WRAP ON
	SET CENTURY ON
	SET EPOCH TO 2000           
	SET DELETED ON
	
	oCn := MySqlConnection( cServer, cDatabase, cUser, cPassword, nPort )
	oCn:Open()
//	oCn:EXECUTE("DROP DATABASE IF EXISTS " + Empresa)
	oCn:EXECUTE("CREATE DATABASE IF NOT EXISTS " + cDatabase )
	oCn:EXECUTE("USE " + cDatabase)

 
	oFiles := Directory( "*.dbf" )

	FOR EACH oFileDbf IN oFiles
		cTable := oFileDbf[ F_NAME ]
		cTable := hb_FNameName( cTable )
		cKeyName:= cTable + "_Id"
		@ 1,10 say "               "
		@ 1,10 say cTable
		USE ( cTable ) EXCLUSIVE
		PACK
		cSql := "CREATE TABLE IF NOT EXISTS " + cTable + " ( " + cKeyName + " INT(11) NOT NULL AUTO_INCREMENT, "
		cSqlInsert := "INSERT INTO " + cTable + " (" 

		oStru := dbStruct()
		FOR nCont = 1 TO Len( oStru )
//			cSql += cTable + "_" + FieldName( nCont ) + " "
//			cSqlInsert += cTable + "_" + FieldName( nCont ) + " "
			cSql += FieldName( nCont ) + " "
			cSqlInsert += FieldName( nCont ) + " "

			DO CASE
				CASE oStru[ nCont, DBS_TYPE ] == "C"
					IF oStru[ nCont, DBS_LEN ] < 250
					   cSql += "VARCHAR(" + Ltrim( Str( oStru[ nCont, DBS_LEN ] ) ) + ") DEFAULT NULL, "
					ELSE
					   cSql += " TEXT, "
					ENDIF		 
				CASE oStru[ nCont, DBS_TYPE ] == "N"
					IF oStru[ nCont, DBS_DEC ] == 0
						IF oStru[ nCont, DBS_LEN ] <= 10
						   cSql += " INT(" + Ltrim( Str( oStru[ nCont, DBS_LEN ] ) ) + ") DEFAULT 0, "
						ELSE
						   cSql += " DOUBLE(" + Ltrim( Str( oStru[ nCont, DBS_LEN ] ) ) + " , " + Ltrim( Str( oStru[ nCont, DBS_DEC ] ) ) + ") DEFAULT 0, "
						ENDIF					
					ELSE
						cSql += " DECIMAL(" + Ltrim( Str( oStru[ nCont, DBS_LEN ] ) ) + " , " + Ltrim( Str( oStru[ nCont, DBS_DEC ] ) ) + ") DEFAULT 0, "
					ENDIF		 
				CASE oStru[ nCont, DBS_TYPE ] == "D"
					cSql += " DATE, " 
				CASE oStru[ nCont, DBS_TYPE ] == "M"
					cSql += " TEXT, "
				CASE oStru[ nCont, DBS_TYPE ] == "L"
					cSql += "TINYINT(1) DEFAULT 0, " 
			ENDCASE		 

		NEXT
//		IF cTable = "ENTRADA"	
//			cSql +="`ENTRADA_XML` TEXT DEFAULT NULL, "
//		ENDIF
		
		
		cSql += "PRIMARY KEY (" + cKeyName + ")) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci"

		oCn:Execute( cSql )   

		IF cTable = "AUTORIZADOS"
			cSqlFix := "INSERT INTO " + cTable + " ( AUTORIZADOS_ID, " 
		ELSEIF cTable = "CAIXA"
			cSqlFix := "INSERT INTO " + cTable + " ( CAIXA_ID, "
		ELSEIF cTable = "CERTIFICADO"
			cSqlFix := "INSERT INTO " + cTable + " ( CERTIFICADO_ID, "
		ELSEIF cTable = "CFOP"
			cSqlFix := "INSERT INTO " + cTable + " ( CFOP_ID, "		
		ELSEIF cTable = "CFOP"
			cSqlFix := "INSERT INTO " + cTable + " ( CFOP_ID, "	
		ELSEIF cTable = "CLIENTES"
			cSqlFix := "INSERT INTO " + cTable + " ( CLIENTES_ID, "
		ELSEIF cTable = "COMPRASITENS"
			cSqlFix := "INSERT INTO " + cTable + " ( COMPRASITENS_ID, " 
		ELSEIF cTable = "COMPRAS"
			cSqlFix := "INSERT INTO " + cTable + " ( COMPRAS_ID, " 
		ELSEIF cTable = "CUPOM"
			cSqlFix := "INSERT INTO " + cTable + " ( CUPOM_ID, " 
		ELSEIF cTable = "DANFE"
			cSqlFix := "INSERT INTO " + cTable + " ( DANFE_ID, " 
		ELSEIF cTable = "ENTRADAITENS"
			cSqlFix := "INSERT INTO " + cTable + " ( ENTRADAITENS_ID, " 
		ELSEIF cTable = "ENTRADA"
			cSqlFix := "INSERT INTO " + cTable + " ( ENTRADA_ID, " 
		ELSEIF cTable = "FLAG"
			cSqlFix := "INSERT INTO " + cTable + " ( FLAG_ID, " 
		ELSEIF cTable = "FORNECEDORES"
			cSqlFix := "INSERT INTO " + cTable + " ( FORNECEDORES_ID, " 
		ELSEIF cTable = "FORPRO"
			cSqlFix := "INSERT INTO " + cTable + " ( FORPRO_ID, " 
		ELSEIF cTable = "LEMBRETE"
			cSqlFix := "INSERT INTO " + cTable + " ( LEMBRETE_ID, " 
		ELSEIF cTable = "LOGGERAL"
			cSqlFix := "INSERT INTO " + cTable + " ( LOGGERAL_ID, "
		ELSEIF cTable = "MOVCAIXA"
			cSqlFix := "INSERT INTO " + cTable + " ( MOVCAIXA_ID, "
		ELSEIF cTable = "NCM"
			cSqlFix := "INSERT INTO " + cTable + " ( NCM_ID, "
		ELSEIF cTable = "PAGAR"
			cSqlFix := "INSERT INTO " + cTable + " ( PAGAR_ID, "
		ELSEIF cTable = "PASSWORD"
			cSqlFix := "INSERT INTO " + cTable + " ( PASSWORD_ID, "
		ELSEIF cTable = "PEDIDOSITENS"
			cSqlFix := "INSERT INTO " + cTable + " ( PEDIDOSITENS_ID, "
		ELSEIF cTable = "PEDIDOS"
			cSqlFix := "INSERT INTO " + cTable + " ( PEDIDOS_ID, " 
		ELSEIF cTable = "PRODUTOS"
			cSqlFix := "INSERT INTO " + cTable + " ( PRODUTOS_ID, " 
		ELSEIF cTable = "RECEBER"
			cSqlFix := "INSERT INTO " + cTable + " ( RECEBER_ID, " 
		ELSEIF cTable = "VENDEDOR"
			cSqlFix := "INSERT INTO " + cTable + " ( VENDEDOR_ID, " 
		ELSE
			cSqlFix := "INSERT INTO " + cTable + " ( " 
		ENDIF
		FOR nCont = 1 TO FCount()
			cSqlFix += FieldName( nCont )
			IF nCont != FCount()
				cSqlFix += ", "
			ENDIF
		NEXT	

		
		cSqlFix += " ) VALUES "
		cTxt := ""		
		DO WHILE ! Eof()
			IF cTable = "AUTORIZADOS"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "	
			ELSEIF cTable = "CAIXA"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "				
			ELSEIF cTable = "CERTIFICADO"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "	
			ELSEIF cTable = "CFOP"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "				
			ELSEIF cTable = "CFOP"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "					
			ELSEIF cTable = "CLIENTES"
				cSql := "( '" + UCLIENTE + "', "
			ELSEIF cTable = "COMPRASITENS"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "COMPRAS"
				cSql := "( '" + NUMPED + "', "
			ELSEIF cTable = "CUPOM"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "DANFE"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "				
			ELSEIF cTable = "ENTRADAITENS"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "ENTRADA"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "FLAG"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "FORNECEDORES"
				cSql := "( '" + UFORNEC + "', "
			ELSEIF cTable = "FORPRO"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "LEMBRETE"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "LOGGERAL"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "MOVCAIXA"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "NCM"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "PAGAR"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "PASSWORD"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "PEDIDOSITENS"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "PEDIDOS"
				cSql := "( '" + NUMNOT + "', "				
			ELSEIF cTable = "PRODUTOS"
				cSql := "( '" + CODPRO + "', "
			ELSEIF cTable = "RECEBER"
				cSql := "( '" + STRZERO(RECNO(),11,0) + "', "
			ELSEIF cTable = "VENDEDOR"
				cSql := "( '" + STRZERO(CODVEN,11,0) + "', "
			ELSE
				cSql := "( "
			ENDIF
			
			FOR nCont = 1 TO Len( oStru )
				cSql += SqlValue( Fieldget( nCont ) )
				IF nCont != FCount()
					cSql += ","
				ENDIF
			NEXT
			cSql += " )"
			IF Len( cTxt ) == 0
				cTxt += cSqlFix
				lBegin := .T.
			ENDIF
			IF ! lBegin
				cTxt += ", "
			ENDIF
			lBegin := .F.
			cTxt += cSql
			IF Len( cTxt ) > MYSQL_MAX_CMDINSERT

				oCn:Execute( cTxt )
				cTxt := ""
			ENDIF
			SKIP
		ENDDO
		IF Len( cTxt ) != 0

			oCn:Execute( cTxt )
		ENDIF
		USE
		
	NEXT

	@ 1,10 say "MUDANDO ESTRUTURA DOS ARQUIVOS"

	oCn:Execute( "ALTER TABLE `flag` ";
				+ "CHANGE COLUMN `N_CNF` `N_CNF` INT(6) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `N_XUF`")

	oCn:Execute( "UPDATE `solucoes`.`flag` SET `B_VERPROC`='SGC V7.0' WHERE  `FLAG_Id`=1")

	oCn:Execute( "UPDATE `solucoes`.`flag` SET `N_CNF`='1' WHERE  `FLAG_Id`=1")


	oCn:Execute( "ALTER TABLE `clientes` DROP COLUMN `UCLIENTE` ")

	oCn:Execute( "ALTER TABLE `compras` CHANGE COLUMN `UFORNEC` `FORNECEDOR_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `COMPRAS_Id`, ";
				+"CHANGE COLUMN `NRAZ_SOC` `NRAZ_SOC` VARCHAR(45) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `FORNECEDOR_ID`, "	;
				+"DROP COLUMN `NUMPED` ")

	oCn:Execute( "ALTER TABLE `comprasitens` CHANGE COLUMN `NUMPED` `COMPRAS_ID` INT(11) NOT NULL COLLATE 'utf8_general_ci' AFTER `COMPRASITENS_Id`, "	;
				+"CHANGE COLUMN `CODPRO` `PRODUTOS_ID` INT(11) NOT NULL COLLATE 'utf8_general_ci' AFTER `COMPRAS_ID`, "	;
				+"CHANGE COLUMN `ITEM` `ITEM` INT(3) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `PRODUTOS_ID`")

	oCn:Execute( "ALTER TABLE `danfe` ";
				+"CHANGE COLUMN `NUMNOT` `PEDIDOS_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `DANFE_Id`")

	oCn:Execute( "ALTER TABLE `danfe` ";
				+ "CHANGE COLUMN `B_NNF` `B_NNF` INT(9) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `B_SERIE`")

	oCn:Execute( "ALTER TABLE `danfe` ";
				+ "ADD COLUMN `CLIENTE_ID` INT(11) NULL DEFAULT NULL AFTER `PEDIDOS_ID`")

	oCn:Execute( "ALTER TABLE `danfe` ";
				+ "ADD COLUMN `TOT001` DECIMAL(14,2) NULL DEFAULT NULL AFTER `QUEM_BLOQ`, " ; 
				+ "ADD COLUMN `TIPPAG` TINYINT(2) NULL DEFAULT NULL AFTER `TOT001`")

	oCn:Execute( "ALTER TABLE `cupom` ";
				+"CHANGE COLUMN `NUMNOT` `PEDIDOS_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `CUPOM_Id`, "	;
				+"CHANGE COLUMN `B_NNF` `B_NNF` INT(9) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `B_SERIE`")

	oCn:Execute( "ALTER TABLE `cupom` ";
				+ "ADD COLUMN `TOT001` DECIMAL(14,2) NULL DEFAULT NULL AFTER `MAQUINA`, ";
				+ "ADD COLUMN `TIPPAG` TINYINT(2) NULL DEFAULT NULL AFTER `TOT001`")

//	oCn:Execute( "ALTER TABLE `comprasitens` ";
//				+"ADD CONSTRAINT `FK_comprasitens_compras` FOREIGN KEY (`COMPRAS_ID`) REFERENCES `compras` (`COMPRAS_Id`) ON DELETE CASCADE	")	

	oCn:Execute( "ALTER TABLE `comprasitens` "; 
				+ "ADD COLUMN `VENDASMES` DECIMAL(14,4) NULL DEFAULT '0.00' AFTER `VLRTOT`")

	oCn:Execute( "ALTER TABLE `movcaixa` 	CHANGE COLUMN `CLIFOR` `CLIFOR` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `TIPMOV` ")

	oCn:Execute( "ALTER TABLE `entrada` CHANGE COLUMN `UFORNEC` `FORNECEDOR_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `ENTRADA_Id`" )
	oCn:Execute( "ALTER TABLE `entrada` ADD COLUMN `ENTRADA_XML` TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `TIPOENTR`" )
	oCn:Execute( "ALTER TABLE `entrada` CHANGE COLUMN `B_NNF` `B_NNF` INT(9) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `B_SERIE`")

	oCn:Execute( "ALTER TABLE `entradaitens` ";
				+"ADD COLUMN `ENTRADA_ID` INT(11)  NULL DEFAULT NULL AFTER `ENTRADAITENS_Id`, ";
				+"CHANGE COLUMN `CODPRO` `PRODUTOS_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `ENTRADA_ID`, ";
				+"CHANGE COLUMN `I_VUNTRIB` `I_VUNTRIB` DECIMAL(14,10) NULL DEFAULT '0.0000000000' COLLATE 'utf8_general_ci' AFTER `I_QTRIB`, "; 
				+"CHANGE COLUMN `CODPROBK` `PRODUTOS_ID_BK` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `S_COFSQTDE`, ";
				+"ADD COLUMN `TIPUNI` VARCHAR(6) NULL DEFAULT NULL AFTER `DESPRO` ")

	oCn:Execute( "ALTER TABLE `entradaitens` ";
				+"ADD COLUMN `ST` TINYINT(1) DEFAULT '0' AFTER `QUEM_BLOQ`"	)		

	oCn:Execute( "ALTER TABLE `flag` ADD COLUMN `NOMEUSUARIO` VARCHAR(40) NULL DEFAULT NULL AFTER `ENTVIADEP`")
	oCn:Execute( "UPDATE `flag` SET  NOMEUSUARIO='DEMONSTRAÇÃO' WHERE FLAG_ID='1'")


	oCn:Execute( "ALTER TABLE `fornecedores` DROP COLUMN `UFORNEC`" )

	oCn:Execute( "ALTER TABLE `forpro` ";
				+"CHANGE COLUMN `UFORNEC` `FORNECEDOR_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `FORPRO_Id`, ";
				+"CHANGE COLUMN `CODPRO` `PRODUTOS_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `FORNECEDOR_ID`")

	oCn:Execute( "ALTER TABLE `forpro` ";
				+"CHANGE COLUMN `I_CPROD` `I_CPROD_ID` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `PRODUTOS_ID`")

	oCn:Execute( "ALTER TABLE `lembrete` ";
				+"ADD COLUMN `DESCRICAO` TEXT NULL DEFAULT NULL AFTER `DESC2`")

//	oCn:Execute( "UPDATE lembrete SET lembrete.DESCRICAO=CONCAT(DESC1,DESC2)")	

	oCn:Execute( "ALTER TABLE `lembrete` ";
				+ "ADD COLUMN `QUEM_BLOQ` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `DESCRICAO`")

//	oCn:Execute( "ALTER TABLE `loggeral` ";
//				+"CHANGE COLUMN `CODPRO` `PRODUTOS_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `LOGGERAL_Id`, ";
//				+"CHANGE COLUMN `CODVEN` `VENDEDOR_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `PRODUTOS_ID`, ";
//				+"CHANGE COLUMN `CLIFOR` `FORNECEDOR_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `VENDEDOR_ID`, ";
//				+"ADD COLUMN `CLIENTE_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `FORNECEDOR_ID` " )

	oCn:Execute( "ALTER TABLE `pagar` ";
				+"CHANGE COLUMN `UFORNEC` `FORNECEDOR_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `PAGAR_Id`")

	oCn:Execute( "ALTER TABLE `pagar` ";
				+"ADD COLUMN `DOCUMENTO` INT(11) NULL DEFAULT NULL AFTER `FORNECEDOR_ID`, ";
				+"ADD COLUMN `PARCELA` INT(3) NULL DEFAULT NULL AFTER `DOCUMENTO`, ";
				+"ADD COLUMN `recibos_id` MEDIUMBLOB NULL DEFAULT NULL AFTER `QUEM_BLOQ`")

	@ 1,10 say "MUDANDO ESTRUTURA DOS ARQUIVOS PARTE 2"

	oCn:Execute( "DELETE FROM pagar WHERE pagar.DATVEN IS NULL")

	oCn:Execute( "ALTER TABLE `pedidos` ";
				+"CHANGE COLUMN `UCLIENTE` `CLIENTE_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `PEDIDOS_Id`, ";
				+"CHANGE COLUMN `CODVEN` `VENDEDOR_ID` INT(11) NULL DEFAULT '0' AFTER `CLIENTE_ID`, ";
				+"DROP COLUMN `NUMNOT`")
	
	oCn:Execute( "ALTER TABLE `pedidos` ";
				+"ADD COLUMN `B_FINNFE` INT(1) NULL DEFAULT NULL AFTER `B_INDPAG`")			

	oCn:Execute( "ALTER TABLE `pedidositens` ";
				+"CHANGE COLUMN `NUMNOT` `PEDIDOS_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `PEDIDOSITENS_Id`, ";
				+"CHANGE COLUMN `CODPRO` `PRODUTOS_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `PEDIDOS_ID`, ";
				+"CHANGE COLUMN `NUMITEM` `ITEM` INT(3) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `PRODUTOS_ID`")
			
	oCn:Execute( "ALTER TABLE `produtos` 	DROP COLUMN `CODPRO`" )


	oCn:Execute( "ALTER TABLE `produtos` ";
				+"ADD COLUMN `CONV_FATOR` TINYINT(1) NULL DEFAULT NULL AFTER `QUEM_BLOQ`, ";
				+"ADD COLUMN `CONV_QTDE` INT(10) NULL DEFAULT NULL AFTER `CONV_FATOR`" )

	oCn:Execute( "ALTER TABLE `receber` "; 
				+"CHANGE COLUMN `UCLIENTE` `CLIENTES_ID` INT(11) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `RECEBER_Id`")

	oCn:Execute( "ALTER TABLE `receber` "; 
				+"ADD COLUMN `RECIBOS_ID` INT(11) NULL DEFAULT NULL AFTER `QUEM_BLOQ")
	
	oCn:Execute( "ALTER TABLE `receber` ";
				+"ADD COLUMN `DOCUMENTO` INT(11) NULL DEFAULT NULL AFTER `CLIENTES_ID`, ";
				+"ADD COLUMN `PARCELA` INT(3) NULL DEFAULT NULL AFTER `DOCUMENTO`")

	oCn:Execute( "ALTER TABLE `receber` ADD COLUMN `MOVCAIXA_ID` INT(11) NULL DEFAULT NULL AFTER `RECIBOS_ID`")

	oCn:Execute( "ALTER TABLE `vendedor` DROP COLUMN `CODVEN`")

	oCn:Execute( "ALTER TABLE `pedidos` ";
				+"ADD COLUMN `VALRECEBIDO` DECIMAL(14,2) NULL DEFAULT '0.00' AFTER `TOTCOM`, ";
				+"CHANGE COLUMN `TROCO` `TROCO` DECIMAL(14,2) NULL DEFAULT '0.00' AFTER `VALRECEBIDO`")	

	oCn:Execute( "ALTER TABLE `pedidos` ";
				+"ADD COLUMN `TOT_PESO` DECIMAL(14,4) NULL DEFAULT '0.00' AFTER `TOT_PROD`")

	@ 1,10 say "MUDANDO ESTRUTURA DOS ARQUIVOS PARTE 3"

	oCn:Execute( "ALTER TABLE `fornecedores` "; 
				+"ADD COLUMN `FL_CLIENTE` TINYINT(1) NULL DEFAULT '0' AFTER `VALOR`, ";
				+"ADD COLUMN `FL_FORNECEDOR` TINYINT(1) NULL DEFAULT '0' AFTER `FL_CLIENTE`, ";
				+"ADD COLUMN `FL_FUNCIONARIO` TINYINT(1) NULL DEFAULT '0' AFTER `FL_FORNECEDOR`, ";
				+"ADD COLUMN `FL_TRANSPORT` TINYINT(1) NULL DEFAULT '0' AFTER `FL_FUNCIONARIO`, ";
				+"ADD COLUMN `FL_EXFUNCIONARIO` TINYINT(1) NULL DEFAULT '0' AFTER `FL_TRANSPORT`, ";
				+"ADD COLUMN `FL_VENDEDOR` TINYINT(1) NULL DEFAULT '0' AFTER `FL_EXFUNCIONARIO`, ";
				+"ADD COLUMN `INSMUN` VARCHAR(18) NULL DEFAULT NULL AFTER `SUFRAMA`, ";
				+"DROP COLUMN `EX_FORN`, ";
				+"DROP COLUMN `ULTVEN`, ";
				+"DROP COLUMN `ULTPAG`, ";
				+"DROP COLUMN `MALDIR`, ";
				+"DROP COLUMN `DATFEC`, ";
				+"DROP COLUMN `DATREC`, ";
				+"DROP COLUMN `CODROT`, ";
				+"DROP COLUMN `ICMS`, ";
				+"DROP COLUMN `TAXCLI`, ";
				+"DROP COLUMN `NUMPAR`, ";
				+"DROP COLUMN `PERDES`, ";
				+"DROP COLUMN `VALMIN` ")
			

	oCn:Execute( "ALTER TABLE `clientes` "; 
				+"ADD COLUMN `FL_CLIENTE` TINYINT(1) NULL DEFAULT '0.00' AFTER `VALOR`, ";
				+"ADD COLUMN `FL_FORNECEDOR` TINYINT(1) NULL DEFAULT '0.00' AFTER `FL_CLIENTE`, ";
				+"ADD COLUMN `FL_FUNCIONARIO` TINYINT(1) NULL DEFAULT '0.00' AFTER `FL_FORNECEDOR`, ";
				+"ADD COLUMN `FL_TRANSPORT` TINYINT(1) NULL DEFAULT '0.00' AFTER `FL_FUNCIONARIO`, ";
				+"ADD COLUMN `FL_EXFUNCIONARIO` TINYINT(1) NULL DEFAULT '0.00' AFTER `FL_TRANSPORT`, ";
				+"ADD COLUMN `FL_VENDEDDOR` TINYINT(1) NULL DEFAULT '0.00' AFTER `FL_EXFUNCIONARIO`, ";
				+"DROP COLUMN `EX_CLIE`, ";
				+"DROP COLUMN `ULTVEN`, ";
				+"DROP COLUMN `ULTPAG`, ";
				+"DROP COLUMN `MALDIR`, ";
				+"DROP COLUMN `DATFEC`, ";
				+"DROP COLUMN `DATREC`, ";
				+"DROP COLUMN `CODROT`, ";
				+"DROP COLUMN `ICMS`, ";
				+"DROP COLUMN `TAXCLI`, ";
				+"DROP COLUMN `NUMPAR`, ";
				+"DROP COLUMN `PERDES`, ";
				+"DROP COLUMN `VALMIN` ")


	oCn:Execute( "ALTER TABLE `fornecedores` "; 
				+"ADD COLUMN `INF_ADICIONAIS` TEXT NULL DEFAULT NULL AFTER `FL_VENDEDOR` ")

	oCn:Execute("UPDATE FORNECEDORES Set FORNECEDORES.FL_FORNECEDOR = '1' WHERE SUBSTR(FORNECEDORES.NRAZ_SOC,1,5) != '*FUNC'")

	oCn:Execute("UPDATE FORNECEDORES Set FORNECEDORES.FL_FUNCIONARIO = '1' WHERE SUBSTR(FORNECEDORES.NRAZ_SOC,1,5) = '*FUNC'")
	
	oQuery1:=oCn:Execute("SELECT * FROM CLIENTES")


	@ 1,10 say "MUDANDO ARQUIVOS PARTE 4 MIGRANDO DADOS FORNECEDOR/CLIENTE"
	

	DO WHILE ! oQuery1:Eof()

			cQuery1	:="INSERT INTO FORNECEDORES ( "
			cQuery2	:=" VALUES ("

			cQuery1 += "FORNECEDORES.FORNECEDORES_ID, "
			cQuery2	+= "'" + strzero(FORNECEDORES_ID,11,0) + "', "
			
			cQuery1 += "FORNECEDORES.TIPCLI, "
			cQuery2	+= "'" + oQuery1:Fields( "TIPCLI" ):Value + "', "
			
			cQuery1 += "FORNECEDORES.NRAZ_SOC, "
			cQuery2	+="'" + oQuery1:Fields( "NRAZ_SOC" ):Value + "', "
			
			cQuery1 += "FORNECEDORES.TIPDOC, "
			cQuery2	+="'"+ oQuery1:Fields( "TIPDOC" ):Value + "', " 
	
			IF ! EMPTY(oQuery1:Fields( "CPFCGC" ):Value)
				cQuery1 += "FORNECEDORES.CPFCGC, "
				cQuery2	+="'" + oQuery1:Fields( "CPFCGC" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "INSEST" ):Value)
				cQuery1 += "FORNECEDORES.INSEST, "
				cQuery2	+="'" + oQuery1:Fields( "INSEST" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "INSMUN" ):Value)
				cQuery1 += "FORNECEDORES.INSMUN, "
				cQuery2	+="'" + oQuery1:Fields( "INSMUN" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "ISENTO" ):Value)
				cQuery1 += "FORNECEDORES.ISENTO, "
				cQuery2	+="'" +  oQuery1:Fields( "ISENTO" ):Value + "', " 	
			else
				cQuery1 += "FORNECEDORES.ISENTO, "
				cQuery2	+="'9', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "SUFRAMA" ):Value)
				cQuery1 += "FORNECEDORES.SUFRAMA, "
				cQuery2	+="'" + oQuery1:Fields( "SUFRAMA" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "EMAIL" ):Value)
				cQuery1 += "FORNECEDORES.EMAIL, "
				cQuery2	+="'" + oQuery1:Fields( "EMAIL" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "ENDER" ):Value)
				cQuery1 += "FORNECEDORES.ENDER, "
				cQuery2	+="'" + oQuery1:Fields( "ENDER" ):Value + "', " 
			ENDIF

			cQuery1 += "FORNECEDORES.NUMERO, "
			cQuery2	+="'" + strzero(oQuery1:Fields( "NUMERO" ):Value,9,0) + "', " 

			IF ! EMPTY(oQuery1:Fields( "COMPLEM" ):Value)
				cQuery1 += "FORNECEDORES.COMPLEM, "
				cQuery2	+="'" + oQuery1:Fields( "COMPLEM" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "BAIR" ):Value)
				cQuery1 += "FORNECEDORES.BAIR, "
				cQuery2	+="'" + oQuery1:Fields( "BAIR" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "CEP" ):Value)
				cQuery1 += "FORNECEDORES.CEP, "
				cQuery2	+="'" + oQuery1:Fields( "CEP" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "PAIS" ):Value)
				cQuery1 += "FORNECEDORES.PAIS, "
				cQuery2 += "'" +  oQuery1:Fields( "PAIS" ):Value + "', " 
			else
				cQuery1 += "FORNECEDORES.PAIS, "
				cQuery2 += "'BRASIL', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "CODPAIS" ):Value)
				cQuery1 += "FORNECEDORES.CODPAIS, "
				cQuery2 += "'" + oQuery1:Fields( "CODPAIS" ):Value + "', "
			else
				cQuery1 += "FORNECEDORES.CODPAIS, "
				cQuery2 += "'1058', "
			ENDIF
			
			IF ! EMPTY(oQuery1:Fields( "UF" ):Value)
				cQuery1 += "FORNECEDORES.UF, "
				cQuery2 += "'"	+ oQuery1:Fields( "UF" ):Value + "', "  
			ENDIF
	
			IF ! EMPTY(oQuery1:Fields( "CIDADE" ):Value)
				cQuery1 += "FORNECEDORES.CIDADE, "
				cQuery2 += "'" + oQuery1:Fields( "CIDADE" ):Value  + "', "  
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "CODMUN" ):Value)
				cQuery1 += "FORNECEDORES.CODMUN, "
				cQuery2 += "'"	+ oQuery1:Fields( "CODMUN" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "FONE" ):Value)
				cQuery1 += "FORNECEDORES.FONE, "
				cQuery2	+="'" + oQuery1:Fields( "FONE" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "LIMCRE" ):Value)
				cQuery1 += "FORNECEDORES.LIMCRE, "
				cQuery2	+="'" + strzero(oQuery1:Fields( "LIMCRE" ):Value,14,2) + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "TOTCRE" ):Value)
				cQuery1 += "FORNECEDORES.TOTCRE, "
				cQuery2	+="'" + strzero(oQuery1:Fields( "TOTCRE" ):Value,14,2) + "', " 			
			ENDIF
			
			IF ! EMPTY(oQuery1:Fields( "VENBLO" ):Value)
				cQuery1 += "FORNECEDORES.VENBLO, "
				cQuery2	+="'" + oQuery1:Fields( "VENBLO" ):Value + "', " 
			ENDIF

			IF ! EMPTY(oQuery1:Fields( "CONTATO" ):Value)
				cQuery1 += "FORNECEDORES.CONTATO, "
				cQuery2	+="'" + oQuery1:Fields( "CONTATO" ):Value + "', " 
			ENDIF

			cQuery1 += "FORNECEDORES.DATCAD, "
			cQuery2	+="'" + Hb_DTOC(oQuery1:Fields( "DATCAD" ):Value, "YYYY-MM-DD") + "', " 
			
			cQuery1 += "FORNECEDORES.DATALT, "
			cQuery2	+="'" + Hb_DTOC(oQuery1:Fields( "DATALT" ):Value, "YYYY-MM-DD") + "', " 
			
			cQuery1 += "FORNECEDORES.USUALT, "
			cQuery2	+="'" + oQuery1:Fields( "USUALT" ):Value + "', " 
	
			cQuery1 += "FORNECEDORES.FL_CLIENTE) "
			cQuery2	+="'1') " 
		
			oCn:Execute(cQuery1+cQuery2)
			
			oQuery2:=oCn:Execute("SELECT pedidos.pedidos_Id, pedidos.CLIENTE_ID	FROM pedidos WHERE pedidos.CLIENTE_ID='" + strzero(oQuery1:Fields( "CLIENTES_ID" ):Value,11,0) + "'")
			oQuery3:=oCn:Execute("SELECT receber.RECEBER_Id, receber.CLIENTES_ID	FROM receber WHERE receber.CLIENTES_ID='" + strzero(oQuery1:Fields( "CLIENTES_ID" ):Value,11,0) + "'")

			if ! oQuery2:Eof()
				oCn:Execute("UPDATE pedidos SET pedidos.CLIENTE_ID = '" + strzero(FORNECEDORES_ID,11,0) + "' WHERE pedidos.CLIENTE_ID = '" + strzero(oQuery1:Fields( "CLIENTES_ID" ):Value,11,0) + "'")
			ENDIF

			if ! oQuery3:Eof()
				oCn:Execute("UPDATE receber SET receber.CLIENTES_ID = '" + strzero(FORNECEDORES_ID,11,0) + "' WHERE receber.CLIENTES_ID = '" + strzero(oQuery1:Fields( "CLIENTES_ID" ):Value,11,0) + "'")
			endif

			FORNECEDORES_ID++
			oQuery1:MoveNext()
	enddo


	@ 1,10 say "MUDANDO ARQUIVOS PARTE 5 MIGRANDO DADOS LEMBRETES                           "
	oQuery1:=oCn:Execute("SELECT * FROM LEMBRETE")

	DO WHILE ! oQuery1:Eof() 

		IF ! EMPTY(oQuery1:Fields( "DESC1" ):Value)
			IF ! EMPTY(oQuery1:Fields( "DESC2" ):Value)
				oCn:Execute("UPDATE LEMBRETE SET lembrete.Descricao = '" + (oQuery1:Fields( "DESC1" ):Value + oQuery1:Fields( "DESC2" ):Value)  + "' WHERE LEMBRETE_ID = '" + STRZERO(oQuery1:Fields( "LEMBRETE_ID" ):Value,11,0) + "'")
			else
				oCn:Execute("UPDATE LEMBRETE SET lembrete.Descricao = '" + (oQuery1:Fields( "DESC1" ):Value + "' WHERE LEMBRETE_ID = '" + STRZERO(oQuery1:Fields( "LEMBRETE_ID" ):Value,11,0) + "'"))
			ENDIF
		ENDIF
		oQuery1:MoveNext()
	enddo

	oCn:Execute( "ALTER TABLE `lembrete` DROP COLUMN `DESC1`, 	DROP COLUMN `DESC2`, 	DROP COLUMN `COD`")


	@ 1,10 say "MUDANDO ARQUIVOS PARTE 6 MIGRANDO DADOS ENTRADA                           "
	oQuery1 := oCn:Execute("SELECT entrada.ENTRADA_ID, entradaitens.ENTRADAITENS_Id FROM entradaitens join entrada on entradaitens.I_ID = entrada.A_ID")

	DO WHILE ! oQuery1:Eof()

		cQuery1:="UPDATE ENTRADAITENS SET ENTRADAITENS.ENTRADA_ID = '" + STRZERO(oQuery1:Fields( "ENTRADA_ID" ):Value,11,0) + "' WHERE ENTRADAITENS.ENTRADAITENS_Id = '" + STRZERO(oQuery1:Fields( "ENTRADAITENS_Id" ):Value,11,0) + "' "
		oCn:Execute( cQuery1 )
		oQuery1:MoveNext()

	ENDDO


	oCn:Execute("CREATE TABLE IF NOT EXISTS `recibos` (`RECIBOS_Id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,`NOME`  VARCHAR(100) NULL DEFAULT NULL,`FICHEIRO` MEDIUMBLOB NOT NULL) COLLATE='utf8_general_ci' ENGINE=InnoDB")

	@ 1,10 say "MUDANDO ARQUIVOS PARTE 6 MIGRANDO DADOS DO CONTAS A PAGAR                       "
	CARREGA_XML_PAGAR( oCn )

	@ 1,10 say "MUDANDO ARQUIVOS PARTE 7 MIGRANDO DADOS DO CONTAS A RECEBER                     "
	CARREGA_XML_RECEBER( oCn )


	oCn:Close()
	QUIT

RETURN

************************************************************************************************
FUNCTION MySqlConnection( cServer, cDatabase, cUser, cPassword, nPort )
************************************************************************************************
   LOCAL cnConnection

   cnConnection:= win_OleCreateObject( "ADODB.Connection" )
   cnConnection:ConnectionString := iif( win_OsIs10(), "Provider=MSDASQL;", "" )
   cnConnection:ConnectionString += "Driver={MariaDB ODBC 3.1 Driver};"
   cnConnection:ConnectionString += ;
      "Server=" + cServer + ";" + ;
      "Port=" +  nPort + ";" + ;
      "Stmt=;" + ;
      "User=" + cUser + ";" + ;
      "Password=" + cPassword + ";" + ;
      "Collation=utf8_general_ci;" + ;
      "AUTO_RECONNECT=1;" + ;
      "COMPRESSED_PROTO=0;" + ;
      "PAD_SPACE=1"
   cnConnection:CursorLocation    := 3
   cnConnection:CommandTimeOut    := 600 // seconds
   cnConnection:ConnectionTimeOut := 600 // seconds

   RETURN cnConnection
************************************************************************************************
FUNCTION win_OsIs10(); RETURN .T.
************************************************************************************************
************************************************************************************************ 
FUNCTION sqlvalue(x)
************************************************************************************************
DO CASE
	CASE ValType(x)=="N"; RETURN ltrim(str(x))
	CASE ValType(x)=="L"; IF( X , X:= "1" , X:= "0"); 	RETURN x
	CASE ValType(x)=="D"; IF(EMPTY( x ) , X:= "NULL", X:= ['] + Transform(Dtos(x),"@R 9999-99-99" ) + [']); RETURN x
	CASE ValType(x)=="C"; IF(EMPTY( x ) , X:= "NULL", X:= ['] + STRTRAN(MySqlValidString(x),"'"," ") + [']); RETURN x
	OTHERWISE; X:= "NULL"; RETURN x 
ENDCASE

************************************************************************************************
STATIC FUNCTION MySqlValidString( xValue )
************************************************************************************************
   xValue := StrTran( xValue, Chr(91),  " " )
   xValue := StrTran( xValue, Chr(93),  " " )
   xValue := StrTran( xValue, Chr(167), " " )
   xValue := StrTran( xValue, Chr(128), "C" )
   xValue := StrTran( xValue, Chr(135), "C" )
   xValue := StrTran( xValue, Chr(166), "A" )
   xValue := StrTran( xValue, Chr(198), "A" )
   xValue := StrTran( xValue, Chr(0),   "" )
   //xValue := StrTran( xValue, Chr(95),  "-" ) // usada em email barra inferior
   xValue := StrTran( xValue, Chr(229), "O" )
   xValue := StrTran( xValue, Chr(124), " " )
   xValue := StrTran( xValue, Chr(141), " " )
   xValue := StrTran( xValue, Chr(181), " " )
   xValue := StrTran( xValue, Chr(162), " " )
   xValue := StrTran( xValue, Chr(224), " " )
   xValue := StrTran( xValue, Chr(133), " " )
   xValue := StrTran( xValue, Chr(144), "E" )
   xValue := StrTran( xValue, Chr(160), " " )

   RETURN xValue
se tiver duvidas pode perguntar

dbf x mysql

Enviado: 07 Out 2020 13:50
por pena
perfeito, já estou adaptando aqui, obg
Todos os horários são UTC-03:00
Página 1 de 1

Powered by phpBB® Forum Software © phpBB Limited

Traduzido por: Suporte phpBB