Moderador: Moderadores
/*********************************************
* 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
Usuários vendo este fórum: Nenhum usuário registrado online e 4 visitantes