public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
FbCommand log = new FbCommand();
FbConnection conn2 = null;
MySqlConnection conn = null;
log.Connection = conn2;
try
{
DataTable dtable = new DataTable();
FbCommand create = new FbCommand($@"
CREATE TABLE cidade_tella (
cod_cidade INT NOT NULL,
cod_estado SMALLINT NOT NULL,
nom_cidade varchar(100) NOT NULL,
municipioibge INT,
sgl_estado VARCHAR(5),
nom_estado VARCHAR(250),
PRIMARY KEY (cod_cidade));", conn2);
create.ExecuteNonQuery();
FbCommand index = new FbCommand($@"CREATE INDEX index_nom_cidade ON cidade_tella (nom_cidade);", conn2);
index.ExecuteNonQuery();
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(@"SELECT c.cod_cidade, c.cod_estado, c.nom_cidade, ifnull(c.municipioibge,0) as municipioibge, e.sgl_estado, e.nom_estado FROM cidade c
JOIN estado e ON (c.cod_estado = e.cod_estado)
WHERE e.cod_estado NOT IN (28);");
MySqlDataAdapter select = new MySqlDataAdapter(queryBuilder.ToString(), conn);
select.Fill(dtable);
StringBuilder queryBuilder2 = new StringBuilder();
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder2.Append("INSERT INTO cidade_tella (cod_cidade,cod_estado,nom_cidade,municipioibge, sgl_estado,nom_estado) VALUES ");
queryBuilder2.Append($@"({dtable.Rows[i]["cod_cidade"]} , {dtable.Rows[i]["cod_estado"]} , '{dtable.Rows[i]["nom_cidade"]}' , {dtable.Rows[i]["municipioibge"]} , '{dtable.Rows[i]["sgl_estado"]}' , '{dtable.Rows[i]["nom_estado"]}'); ");
FbCommand query = new FbCommand(queryBuilder2.ToString(), conn2);
query.ExecuteNonQuery();
queryBuilder2.Clear();
}
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from cidade_tella;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
MySqlCommand insert = new MySqlCommand("", conn);
insert.CommandTimeout = 86400;
try
{
DataTable dtable = new DataTable();
MySqlCommand create = new MySqlCommand(
$@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_aluno_siga;
DELETE FROM aluno;
CREATE TABLE `mig_aluno_siga` (
`codaluno` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`nomealuno` VARCHAR(100) NOT NULL,
`endereco` VARCHAR(200) NULL DEFAULT NULL,
`bairro` VARCHAR(60) NULL DEFAULT NULL,
`uf` SMALLINT(6) NULL DEFAULT NULL,
`cidade` varchar(100) NULL DEFAULT NULL,
`cep` VARCHAR(20) NULL DEFAULT NULL,
`telresid` VARCHAR(20) NULL DEFAULT NULL,
`celular` VARCHAR(20) NULL DEFAULT NULL,
`email` VARCHAR(100) NULL DEFAULT NULL,
`sexo` CHAR(1) NULL DEFAULT NULL,
`dtnascimento` varchar(100) NULL DEFAULT NULL,
`ufnaturalidade` SMALLINT(6) UNSIGNED NULL DEFAULT NULL,
`cidadenaturalidade` varchar(100) NULL DEFAULT NULL,
`cor` VARCHAR(13) NULL DEFAULT NULL,
`pai` VARCHAR(100) NULL DEFAULT NULL,
`trabpai` varchar(100) NULL DEFAULT NULL,
`profpai` VARCHAR(40) NULL DEFAULT NULL,
`telpai` VARCHAR(11) NULL DEFAULT NULL,
`celularpai` VARCHAR(11) NULL DEFAULT NULL,
`emailpai` VARCHAR(100) NULL DEFAULT NULL,
`mae` VARCHAR(100) NULL DEFAULT NULL,
`trabmae` varchar(100) NULL DEFAULT NULL,
`profmae` VARCHAR(40) NULL DEFAULT NULL,
`telmae` VARCHAR(11) NULL DEFAULT NULL,
`celularmae` VARCHAR(11) NULL DEFAULT NULL,
`emailmae` VARCHAR(100) NULL DEFAULT NULL,
`obs` VARCHAR(600) NULL DEFAULT NULL,
`cpf` VARCHAR(11) NULL DEFAULT NULL,
`rg` varchar(100) NULL DEFAULT NULL,
`foto` VARCHAR(100) NULL DEFAULT NULL,
`senha` VARCHAR(8) NULL DEFAULT NULL,
`nomeabrev` VARCHAR(40) NULL DEFAULT NULL,
`certidaonumero` VARCHAR(40) NULL DEFAULT NULL,
`certidaolivrofolha` VARCHAR(30) NULL DEFAULT NULL,
`certidaocartorio` varchar(100) NULL DEFAULT NULL,
`religiao` VARCHAR(40) NULL DEFAULT NULL,
`codeducacaoespecial` SMALLINT(6) NULL DEFAULT NULL,
`obseducacaoespecial` VARCHAR(500) NULL DEFAULT NULL,
`codsocio` INT(11) NULL DEFAULT NULL,
`vivopai` CHAR(1) NULL DEFAULT 'S',
`instrucaopai` VARCHAR(40) NULL DEFAULT NULL,
`enderecopai` VARCHAR(200) NULL DEFAULT NULL,
`bairropai` VARCHAR(60) NULL DEFAULT NULL,
`codcidadepai` varchar(100) NULL DEFAULT NULL,
`codufpai` SMALLINT(6) NULL DEFAULT NULL,
`ceppai` VARCHAR(8) NULL DEFAULT NULL,
`vivomae` CHAR(1) NULL DEFAULT 'S',
`instrucaomae` VARCHAR(40) NULL DEFAULT NULL,
`enderecomae` VARCHAR(200) NULL DEFAULT NULL,
`bairromae` VARCHAR(60) NULL DEFAULT NULL,
`cepmae` VARCHAR(8) NULL DEFAULT NULL,
`codufmae` SMALLINT(6) NULL DEFAULT NULL,
`codcidademae` varchar(100) NULL DEFAULT NULL,
`ativo` CHAR(1) NULL DEFAULT 'S',
`fotoPortal` VARCHAR(100) NULL DEFAULT NULL,
`foneportal` VARCHAR(10) NULL DEFAULT NULL,
`emailportal` VARCHAR(60) NULL DEFAULT NULL,
`titeleitor` VARCHAR(15) NULL DEFAULT NULL,
`zona` VARCHAR(10) NULL DEFAULT NULL,
`dtcadastro` varchar(100) NULL DEFAULT NULL,
`usercadastro` INT(11) NULL DEFAULT NULL,
`dtalteracao` varchar(100) NULL DEFAULT NULL,
`useralteracao` INT(11) NULL DEFAULT NULL,
`nacionalidade` VARCHAR(30) NULL DEFAULT NULL,
`bloqueado` CHAR(1) NULL DEFAULT NULL,
`obshistfundamental` VARCHAR(3000) NULL DEFAULT NULL,
`obshistmedio` VARCHAR(3000) NULL DEFAULT NULL,
`estadocivilpai` varchar(100) NULL DEFAULT NULL,
`estadocivilmae` varchar(100) NULL DEFAULT NULL,
`dtnascimentopai` varchar(100) NULL DEFAULT NULL,
`dtnascimentomae` varchar(100) NULL DEFAULT NULL,
`codpaisnaturalidade` INT(11) NULL DEFAULT NULL,
`alunoadotado` CHAR(1) NULL DEFAULT NULL,
`sabeadocao` CHAR(1) NULL DEFAULT NULL,
`paisseparados` CHAR(1) NULL DEFAULT NULL,
`tiposanguineo` varchar(100) NULL DEFAULT NULL,
`planosaude` VARCHAR(100) NULL DEFAULT NULL,
`alergia` VARCHAR(100) NULL DEFAULT NULL,
`doencacongenita` varchar(100) NULL DEFAULT NULL,
`deficiencia` varchar(100) NULL DEFAULT NULL,
`cuidadoespecial` VARCHAR(100) NULL DEFAULT NULL,
`locomocaoida` VARCHAR(40) NULL DEFAULT NULL,
`locomocaovolta` VARCHAR(40) NULL DEFAULT NULL,
`obsguiatransferencia` VARCHAR(500) NULL DEFAULT NULL,
`naturalidade` VARCHAR(100) NULL DEFAULT NULL,
`cpfpai` VARCHAR(11) NULL DEFAULT NULL,
`rgpai` varchar(100) NULL DEFAULT NULL,
`cpfmae` VARCHAR(11) NULL DEFAULT NULL,
`rgmae` varchar(100) NULL DEFAULT NULL,
`fotoblob` BLOB NULL,
`estadocivil` varchar(100) NULL DEFAULT NULL,
`saisozinho` CHAR(1) NULL DEFAULT NULL,
`tipocertidao` CHAR(1) NULL DEFAULT 'N',
`certreservista` VARCHAR(40) NULL DEFAULT NULL,
`profissao` VARCHAR(40) NULL DEFAULT NULL,
`localtrabalho` VARCHAR(100) NULL DEFAULT NULL,
`teltrabalho` VARCHAR(10) NULL DEFAULT NULL,
`numcartao` varchar(100) NULL DEFAULT NULL,
`enviarsms` CHAR(1) NULL DEFAULT NULL,
`celularsms` VARCHAR(11) NULL DEFAULT NULL,
`operadorasms` CHAR(1) NULL DEFAULT NULL,
`hospital` VARCHAR(100) NULL DEFAULT NULL,
`pne` CHAR(1) NULL DEFAULT 'N' COMMENT 'Aluno portado de necessidades especiais (S/N)',
`pnedescricao` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Descricao na necessidade especial',
`pneavaliado` CHAR(1) NULL DEFAULT 'S' COMMENT 'Aluno é avalidado com notas como os outros alunos',
`alunofalecido` CHAR(1) NULL DEFAULT 'N',
`codcenso` varchar(100) NULL DEFAULT NULL,
`codportaria` INT(11) NULL DEFAULT NULL,
`codpaisorigem` SMALLINT(6) NULL DEFAULT NULL,
`zonaresidencia7024` INT(1) NULL DEFAULT NULL COMMENT '1-Urbana/2-Rural',
`rgorgaoemissor` INT(2) NULL DEFAULT NULL COMMENT 'Orgao Emissor da indentidade. Relaciona com a tabela orgaoemissoridentidade',
`rgestado` SMALLINT(6) NULL DEFAULT NULL COMMENT 'Estado Identidade',
`rgdataemissao` DATE NULL DEFAULT NULL COMMENT 'Data de Emissao RG',
`rgcomplemento` VARCHAR(4) NULL DEFAULT NULL COMMENT 'Complemento RG',
`modelocertidao` INT(1) NULL DEFAULT NULL COMMENT '1-Modelo Antigo / 2-Modelo Novo',
`codcartoriocertidao` INT(11) NULL DEFAULT NULL COMMENT 'codigo do cartorio que emitiu a certidao',
`dtemissaocertidao` DATE NULL DEFAULT NULL COMMENT 'Data da emissão da certidao',
`rgorgaoemissorpai` INT(2) NULL DEFAULT NULL COMMENT 'Orgao Emissor da indentidade. Relaciona com a tabela orgaoemissoridentidade',
`rgestadopai` SMALLINT(6) NULL DEFAULT NULL COMMENT 'Estado Identidade',
`rgdataemissaopai` DATE NULL DEFAULT NULL COMMENT 'Data de Emissao RG',
`nacionalidadepai` varchar(100) NULL DEFAULT NULL,
`codpaisorigempai` SMALLINT(6) NULL DEFAULT NULL,
`codcidadenaturalidadepai` varchar(100) NULL DEFAULT NULL,
`codescolaridadepai` INT(11) NULL DEFAULT NULL,
`rgorgaoemissormae` INT(2) NULL DEFAULT NULL COMMENT 'Orgao Emissor da indentidade. Relaciona com a tabela orgaoemissoridentidade',
`rgestadomae` SMALLINT(6) NULL DEFAULT NULL COMMENT 'Estado Identidade',
`rgdataemissaomae` DATE NULL DEFAULT NULL COMMENT 'Data de Emissao RG',
`nacionalidademae` varchar(100) NULL DEFAULT NULL,
`codpaisorigemmae` SMALLINT(6) NULL DEFAULT NULL,
`codcidadenaturalidademae` varchar(100) NULL DEFAULT NULL,
`codescolaridademae` INT(11) NULL DEFAULT NULL,
`codrendafamiliar` INT(11) NULL DEFAULT NULL,
`obsfinanceiro` VARCHAR(600) NULL DEFAULT NULL,
`obsacademico` VARCHAR(600) NULL DEFAULT NULL,
`sexomae` CHAR(1) NULL DEFAULT 'F',
`sexopai` CHAR(1) NULL DEFAULT 'M',
`codbolsapadrao` INT(11) NULL DEFAULT NULL,
`diavencpadrao` INT(11) NULL DEFAULT NULL,
`ra` varchar(100) NULL DEFAULT NULL,
`localbatalhaomilitar` VARCHAR(100) NULL DEFAULT NULL,
`categoriamilitar` varchar(100) NULL DEFAULT NULL,
`situacaomilitar` varchar(100) NULL DEFAULT NULL,
`dataexpedicaomilitar` DATE NULL DEFAULT NULL,
`secaoeleitor` VARCHAR(10) NULL DEFAULT NULL,
`dataexpedicaoeleitor` DATE NULL DEFAULT NULL,
`avatoken` VARCHAR(200) NULL DEFAULT NULL COMMENT 'token de autenticação do aluno no ava json (login,senha) convertido em base64',
`avauserid` INT(11) NULL DEFAULT NULL,
`obshistinfantil` VARCHAR(3000) NULL DEFAULT NULL,
`idgoogledrive` VARCHAR(100) NULL DEFAULT NULL,
`codescolaridadealuno` INT(11) NULL DEFAULT NULL,
`codaluno_tella` INT (11) NULL DEFAULT NULL,
INDEX `idx_codaluno` (`codaluno`) USING BTREE,
PRIMARY KEY (`codaluno`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;", conn);
create.ExecuteNonQuery();
//cria a funcao REMOVEcaractere e validaemail
MySqlCommand functions = new MySqlCommand($@"
DROP FUNCTION IF EXISTS REMOVEcaractere;
CREATE DEFINER=`root`@`%` FUNCTION `REMOVEcaractere`(`P_TEXTO` VARCHAR(200))
RETURNS char(50) CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
RETURN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(P_TEXTO,'.',''),'-',''),'/',''),' ',''),'_',''),';',''),')',''),'(',''), ' ', ''),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'r',''),'s',''),'t',''),'u',''),'x',''),'z',''),'w',''),'y',''),'|',''),'\'',''),',',''),'$',''),'!',''),'@',''),'+',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'R',''),'S',''),'T',''),'U',''),'V',''),'X',''),'Z',''),'W',''),'Y',''),'á',''),'à',''),'ã',''),'â',''),'Á',''),'À',''),'Ã',''),'Â',''),'é',''),'è',''),'ê',''),'É',''),'È',''),'Ê',''),'í',''),'ì',''),'î',''),'Í',''),'Ì',''),'Î',''),'ó',''),'ò',''),'ô',''),'õ',''),'Ó',''),'Ò',''),'Ô',''),'Õ',''),'ú',''),'ù',''),'û',''),'Ú',''),'Ù',''),'Û',''),'v',''),'q',''),'Q','');
DROP FUNCTION IF EXISTS VALIDAEMAIL;
CREATE DEFINER=`root`@`%` FUNCTION `VALIDAEMAIL`(`P_EMAIL` VARCHAR(200))
RETURNS char(1) CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE V_AUX INT;
DECLARE V_AUX2 CHAR(1);
SET V_AUX = 0;
SET V_AUX = (SELECT COUNT(1) FROM (select P_EMAIL from dual
where P_EMAIL not regexp '[^a-z,0-9,@,.,_,-]'
and P_EMAIL like '%_@_%_.__%'
and P_EMAIL not like '%@@%'
and P_EMAIL not like '%@'
and P_EMAIL not like '%.'
and P_EMAIL not like '%\_'
and P_EMAIL not like '%-'
and P_EMAIL not like '-%'
and P_EMAIL not like '%_@_%_..%' ) AS T);
IF(V_AUX = 0) THEN
SET V_AUX2 = 'F';
ELSE
SET V_AUX2 = 'V';
END IF;
RETURN V_AUX2;
END", conn);
functions.ExecuteNonQuery();
FbCommand MySelect = new FbCommand(@"select
replace(g.prontuario,'-','') as codaluno,
replace(upper(max((rtrim(g.nome)||' '||ltrim(g.sobrenome)))), '''', '') as nomealuno,
replace(replace(upper(max((coalesce(g.endereco, '')||' '||coalesce(g.numero, '')||' '||coalesce(g.complement, '')))),';',','), '''', '') as endereco,
replace(upper(max(g.bairro)), '''', '') as bairro,
max(tt.cod_cidade) as cidade,
max(replace(g.cep,'-','')) as cep,
max(iif((char_length(replace(replace(g.foneres, '-', ''),'.','')) = 8), ('11' || replace(replace(g.foneres, '-', ''),'.','')), null)) as telresid,
max(iif(char_length(replace(replace(replace(replace(replace(g.fonecel,'-',''),'.',''),'Pai',''),'Mãe',''),'Gabriel','')) = 9, '11' || replace(replace(replace(replace(replace(g.fonecel,'-',''),'.',''),'Pai',''),'Mãe',''),'Gabriel',''), null)) as celular,
max(iif(g.email = '',null,g.email)) as email,
max(g.sexo) as sexo,
max(EXTRACT(YEAR FROM g.nascimento) || '-' || EXTRACT(MONTH FROM g.nascimento) || '-' || EXTRACT(DAY FROM g.nascimento)) as dtnascimento,
max(tt.cod_cidade) as cidadenaturalidade,
replace(upper(max(g.pai)), '''', '') as pai,
replace(replace(upper(max(replace(left(g.profi_pai, 40),'/',''))), '''', ''), '\', '') as profpai,
replace(max(iif(char_length(replace(replace(replace(replace(replace(g.fonepai,'-',''),'.',''),'COML',''),'R',''),'COMERCIAL','')) = 8, '11' || replace(replace(replace(replace(replace(g.fonepai,'-',''),'.',''),'COML',''),'R',''),'COMERCIAL',''), null)), '''', '') as telpai,
replace(max(iif(char_length(replace(replace(replace(replace(g.celpai, '-',''),'.',''),'BRASIL',''),'COML','')) = 9, '11' || replace(replace(replace(replace(g.celpai, '-',''),'.',''),'BRASIL',''),'COML',''), null)), '''', '') as celularpai,
replace(max(g.emailpai),';','|') as emailpai,
replace(max(g.mae), '''', '') as mae,
replace(replace(left(max(g.profi_mae),40), '''', ''), '\', '') as profmae,
replace(max(iif(char_length(replace(replace(replace(replace(replace(replace(g.fonemae,'-',''),'.',''),'COML',''),'COKM',''),' ',''),'/','')) = 8, '11' || replace(replace(replace(replace(replace(replace(g.fonemae,'-',''),'.',''),'COML',''),'COKM',''),' ',''),'/',''), null)), '''', '') as telmae,
replace(max(iif(char_length(replace(replace(replace(replace(replace(g.celmae,'-',''),'.',''),'COML',''),'(',''),')','')) = 9, '11' || replace(replace(replace(replace(replace(g.celmae,'-',''),'.',''),'COML',''),'(',''),')',''), iif(char_length(replace(replace(replace(replace(replace(g.celmae,'-',''),'.',''),'COML',''),'(',''),')','')) = 11, replace(replace(replace(replace(replace(g.celmae,'-',''),'.',''),'COML',''),'(',''),')',''), null))), '''', '') as celularmae,
replace(max(g.emailmae),';','|') as emailmae,
max(g.cpf) as cpf,
replace(max(g.rg), '''', '') as rg,
'' as certidaonumero,
'' as certidaolivrofolha,
'' as certidaocartorio,
replace(replace(max(iif(g.end_pai is null, '', g.end_pai) || iif(g.num_pai is null, '', g.num_pai)),';',','), '''', '') as enderecopai,
replace(max(g.bair_pai), '''', '') as bairropai,
max(left(replace(replace(g.cep_pai,'-',''),'.',''),8)) as ceppai,
replace(replace(max(iif(g.end_mae is null, '', g.end_mae) || iif(g.num_mae is null, '', g.num_mae)),';',','), '''', '') as enderecomae,
replace(replace(max(g.bair_mae),';',','), '''', '') as bairromae,
max(left(replace(replace(g.cep_mae,'-',''),'.',''),8)) as cepmae,
max(na.descricao) as nacionalidade,
max(np.descricao) as nacionalidadepai,
max(nm.descricao) as nacionalidademae,
max(EXTRACT(YEAR FROM g.nascpai) || '-' || EXTRACT(MONTH FROM g.nascpai) || '-' || EXTRACT(DAY FROM g.nascpai)) as dtnascimentopai,
max(EXTRACT(YEAR FROM g.nascmae) || '-' || EXTRACT(MONTH FROM g.nascmae) || '-' || EXTRACT(DAY FROM g.nascmae)) as dtnascimentomae,
max(g.estcivpai) as estadocivilpai,
max(g.estcivmae) as estadocivilmae,
max(g.cidadenasc) as naturalidade,
max(g.cpfpai) as cpfpai,
replace(max(g.rgpai), '''', '') as rgpai,
max(g.cpfmae) as cpfmae,
replace(max(g.rgmae), '''', '') as rgmae,
replace(max('TEL: ' || coalesce(g.foneres, ' ') || ' CEL: ' || coalesce(g.fonecel,' ') || ' TELPAI: ' || coalesce(g.fonepai, ' ') || ' CELPAI: ' || coalesce(g.celpai, ' ') || ' TELMAE: ' || coalesce(g.fonemae,' ') || ' CELMAE: ' || coalesce(g.celmae, ' ') ), '''', '') as obs
from siggeral g
left join cidade_tella tt on tt.nom_cidade = upper(g.cidade)
left join signacio na on na.codigo = g.nacio
left join signacio np on np.codigo = g.nacio_pai
left join signacio nm on nm.codigo = g.nacio_mae
group by replace(g.prontuario,'-','');", conn2);
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
try
{
queryBuilder.Append("INSERT INTO mig_aluno_siga (CODALUNO, NOMEALUNO, ENDERECO, BAIRRO, CIDADE, CEP, TELRESID, CELULAR, EMAIL, SEXO, DTNASCIMENTO, CIDADENATURALIDADE, PAI, PROFPAI, TELPAI, CELULARPAI, EMAILPAI, MAE, PROFMAE, TELMAE, CELULARMAE, EMAILMAE, CPF, RG, CERTIDAONUMERO, CERTIDAOLIVROFOLHA, CERTIDAOCARTORIO, ENDERECOPAI, BAIRROPAI, CEPPAI, ENDERECOMAE, BAIRROMAE, CEPMAE, NACIONALIDADE, NACIONALIDADEPAI, NACIONALIDADEMAE, DTNASCIMENTOPAI, DTNASCIMENTOMAE, ESTADOCIVILPAI, ESTADOCIVILMAE, NATURALIDADE, CPFPAI, RGPAI, CPFMAE, RGMAE, OBS) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["CODALUNO"]}' , '{dtable.Rows[i]["NOMEALUNO"]}' , '{dtable.Rows[i]["ENDERECO"]}' , '{dtable.Rows[i]["BAIRRO"]}' , '{dtable.Rows[i]["CIDADE"]}' , '{dtable.Rows[i]["CEP"]}' , '{dtable.Rows[i]["TELRESID"]}' , '{dtable.Rows[i]["CELULAR"]}' , '{dtable.Rows[i]["EMAIL"]}' , '{dtable.Rows[i]["SEXO"]}' , '{dtable.Rows[i]["DTNASCIMENTO"]}' , '{dtable.Rows[i]["CIDADENATURALIDADE"]}' , '{dtable.Rows[i]["PAI"]}' , '{dtable.Rows[i]["PROFPAI"]}' , '{dtable.Rows[i]["TELPAI"]}' , '{dtable.Rows[i]["CELULARPAI"]}' , '{dtable.Rows[i]["EMAILPAI"]}' , '{dtable.Rows[i]["MAE"]}' , '{dtable.Rows[i]["PROFMAE"]}' , '{dtable.Rows[i]["TELMAE"]}' , '{dtable.Rows[i]["CELULARMAE"]}' , '{dtable.Rows[i]["EMAILMAE"]}' , '{dtable.Rows[i]["CPF"]}' , '{dtable.Rows[i]["RG"]}' , '{dtable.Rows[i]["CERTIDAONUMERO"]}' , '{dtable.Rows[i]["CERTIDAOLIVROFOLHA"]}' , '{dtable.Rows[i]["CERTIDAOCARTORIO"]}' , '{dtable.Rows[i]["ENDERECOPAI"]}' , '{dtable.Rows[i]["BAIRROPAI"]}' , '{dtable.Rows[i]["CEPPAI"]}' , '{dtable.Rows[i]["ENDERECOMAE"]}' , '{dtable.Rows[i]["BAIRROMAE"]}' , '{dtable.Rows[i]["CEPMAE"]}' , '{dtable.Rows[i]["NACIONALIDADE"]}' , '{dtable.Rows[i]["NACIONALIDADEPAI"]}' , '{dtable.Rows[i]["NACIONALIDADEMAE"]}' , '{dtable.Rows[i]["DTNASCIMENTOPAI"]}' , '{dtable.Rows[i]["DTNASCIMENTOMAE"]}' , '{dtable.Rows[i]["ESTADOCIVILPAI"]}' , '{dtable.Rows[i]["ESTADOCIVILMAE"]}' , '{dtable.Rows[i]["NATURALIDADE"]}' , '{dtable.Rows[i]["CPFPAI"]}' , '{dtable.Rows[i]["RGPAI"]}' , '{dtable.Rows[i]["CPFMAE"]}' , '{dtable.Rows[i]["RGMAE"]}' , '{dtable.Rows[i]["OBS"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
insert = new MySqlCommand(queryBuilder.ToString(), conn);
insert.ExecuteNonQuery();
}
catch (Exception err)
{
if (err.Message == "Packets larger than max_allowed_packet are not allowed.")
{
for (int i = 0; i < dtable.Rows.Count; i++)
{
insert.CommandText = $@"INSERT INTO mig_aluno_siga (CODALUNO, NOMEALUNO, ENDERECO, BAIRRO, CIDADE, CEP, TELRESID, CELULAR, EMAIL, SEXO, DTNASCIMENTO, CIDADENATURALIDADE, PAI, PROFPAI, TELPAI, CELULARPAI, EMAILPAI, MAE, PROFMAE, TELMAE, CELULARMAE, EMAILMAE, CPF, RG, CERTIDAONUMERO, CERTIDAOLIVROFOLHA, CERTIDAOCARTORIO, ENDERECOPAI, BAIRROPAI, CEPPAI, ENDERECOMAE, BAIRROMAE, CEPMAE, NACIONALIDADE, NACIONALIDADEPAI, NACIONALIDADEMAE, DTNASCIMENTOPAI, DTNASCIMENTOMAE, ESTADOCIVILPAI, ESTADOCIVILMAE, NATURALIDADE, CPFPAI, RGPAI, CPFMAE, RGMAE, OBS) VALUES
('{dtable.Rows[i]["CODALUNO"]}' , '{dtable.Rows[i]["NOMEALUNO"]}' , '{dtable.Rows[i]["ENDERECO"]}' , '{dtable.Rows[i]["BAIRRO"]}' , '{dtable.Rows[i]["CIDADE"]}' , '{dtable.Rows[i]["CEP"]}' , '{dtable.Rows[i]["TELRESID"]}' , '{dtable.Rows[i]["CELULAR"]}' , '{dtable.Rows[i]["EMAIL"]}' , '{dtable.Rows[i]["SEXO"]}' , '{dtable.Rows[i]["DTNASCIMENTO"]}' , '{dtable.Rows[i]["CIDADENATURALIDADE"]}' , '{dtable.Rows[i]["PAI"]}' , '{dtable.Rows[i]["PROFPAI"]}' , '{dtable.Rows[i]["TELPAI"]}' , '{dtable.Rows[i]["CELULARPAI"]}' , '{dtable.Rows[i]["EMAILPAI"]}' , '{dtable.Rows[i]["MAE"]}' , '{dtable.Rows[i]["PROFMAE"]}' , '{dtable.Rows[i]["TELMAE"]}' , '{dtable.Rows[i]["CELULARMAE"]}' , '{dtable.Rows[i]["EMAILMAE"]}' , '{dtable.Rows[i]["CPF"]}' , '{dtable.Rows[i]["RG"]}' , '{dtable.Rows[i]["CERTIDAONUMERO"]}' , '{dtable.Rows[i]["CERTIDAOLIVROFOLHA"]}' , '{dtable.Rows[i]["CERTIDAOCARTORIO"]}' , '{dtable.Rows[i]["ENDERECOPAI"]}' , '{dtable.Rows[i]["BAIRROPAI"]}' , '{dtable.Rows[i]["CEPPAI"]}' , '{dtable.Rows[i]["ENDERECOMAE"]}' , '{dtable.Rows[i]["BAIRROMAE"]}' , '{dtable.Rows[i]["CEPMAE"]}' , '{dtable.Rows[i]["NACIONALIDADE"]}' , '{dtable.Rows[i]["NACIONALIDADEPAI"]}' , '{dtable.Rows[i]["NACIONALIDADEMAE"]}' , '{dtable.Rows[i]["DTNASCIMENTOPAI"]}' , '{dtable.Rows[i]["DTNASCIMENTOMAE"]}' , '{dtable.Rows[i]["ESTADOCIVILPAI"]}' , '{dtable.Rows[i]["ESTADOCIVILMAE"]}' , '{dtable.Rows[i]["NATURALIDADE"]}' , '{dtable.Rows[i]["CPFPAI"]}' , '{dtable.Rows[i]["RGPAI"]}' , '{dtable.Rows[i]["CPFMAE"]}' , '{dtable.Rows[i]["RGMAE"]}' , '{dtable.Rows[i]["OBS"]}');";
insert.ExecuteNonQuery();
}
}
else
{
MessageBox.Show(err.Message);
conn2.Close();
conn.Close();
}
}
MySqlCommand update = new MySqlCommand(@"
update mig_aluno_siga set dtnascimento = null where dtnascimento = '';
update mig_aluno_siga set dtnascimentopai = null where dtnascimentopai = '';
update mig_aluno_siga set dtnascimentomae = null where dtnascimentomae = '';
update mig_aluno_siga set dtcadastro = null where dtcadastro = '';
update mig_aluno_siga set dtalteracao = null where dtalteracao = '';
update mig_aluno_siga set cidade = null where cidade = '';
update mig_aluno_siga set cidadenaturalidade = null where cidadenaturalidade = '';
update mig_aluno_siga set codcidadenaturalidadepai = null where codcidadenaturalidadepai = '';
update mig_aluno_siga set codcidadenaturalidademae = null where codcidadenaturalidademae = '';
update mig_aluno_siga set codcidadepai = null where codcidadepai = '';
update mig_aluno_siga set codcidademae = null where codcidademae = '';", conn);
update.ExecuteNonQuery();
MySqlCommand insertAluno = new MySqlCommand($@"SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO aluno (codaluno, nomealuno, endereco, bairro, uf, cidade, cep, telresid, celular, email, sexo, dtnascimento, ufnaturalidade, cidadenaturalidade, cor, pai, trabpai, profpai, telpai, celularpai, emailpai, mae, trabmae, profmae, telmae, celularmae, emailmae, obs, cpf, rg, foto, senha, nomeabrev, certidaonumero, certidaolivrofolha, certidaocartorio, religiao, codeducacaoespecial, obseducacaoespecial, codsocio, vivopai, instrucaopai, enderecopai, bairropai, codcidadepai, codufpai, ceppai, vivomae, instrucaomae, enderecomae, bairromae, cepmae, codufmae, codcidademae, ativo, fotoPortal, foneportal, emailportal, titeleitor, zona, dtcadastro, usercadastro, dtalteracao, useralteracao, nacionalidade, bloqueado, obshistfundamental, obshistmedio, estadocivilpai, estadocivilmae, dtnascimentopai, dtnascimentomae, codpaisnaturalidade, alunoadotado, sabeadocao, paisseparados, tiposanguineo, planosaude, alergia, doencacongenita, deficiencia, cuidadoespecial, locomocaoida, locomocaovolta, obsguiatransferencia, naturalidade, cpfpai, rgpai, cpfmae, rgmae, fotoblob, estadocivil, saisozinho, tipocertidao, certreservista, profissao, localtrabalho, teltrabalho, numcartao, enviarsms, celularsms, operadorasms, hospital, pne, pnedescricao, pneavaliado, alunofalecido, codcenso, codportaria, codpaisorigem, zonaresidencia7024, rgorgaoemissor, rgestado, rgdataemissao, rgcomplemento, modelocertidao, codcartoriocertidao, dtemissaocertidao, rgorgaoemissorpai, rgestadopai, rgdataemissaopai, nacionalidadepai, codpaisorigempai, codcidadenaturalidadepai, codescolaridadepai, rgorgaoemissormae, rgestadomae, rgdataemissaomae, nacionalidademae, codpaisorigemmae, codcidadenaturalidademae, codescolaridademae, codrendafamiliar, obsfinanceiro, obsacademico, sexomae, sexopai, codbolsapadrao, diavencpadrao, ra, localbatalhaomilitar, categoriamilitar, situacaomilitar, dataexpedicaomilitar, secaoeleitor, dataexpedicaoeleitor, avatoken, avauserid, obshistinfantil, idgoogledrive, codescolaridadealuno)
(SELECT
MAX(codaluno) AS codaluno,
UPPER(nomealuno) AS nomealuno,
UPPER(endereco) AS endereco,
UPPER(bairro) AS bairro,
uf,
cidade,
left(REMOVEcaractere(cep),8) AS cep,
if(length(REMOVEcaractere(telresid)) IN (8,10),REMOVEcaractere(telresid),if(length(REMOVEcaractere(celular)) IN (8,10),REMOVEcaractere(celular),NULL) ) AS telresid,
if(length(REMOVEcaractere(celular)) IN (9,11),REMOVEcaractere(celular),if(length(REMOVEcaractere(telresid)) IN (9,11),REMOVEcaractere(telresid),NULL)) AS celular,
if(validaemail(email) = 'V', email, NULL) AS email,
UPPER(sexo) AS sexo,
dtnascimento,
ufnaturalidade,
cidadenaturalidade,
UPPER(cor) AS cor,
UPPER(pai) AS pai,
UPPER(trabpai) AS trabpai,
left(UPPER(profpai),40) AS profpai,
if(length(REMOVEcaractere(telpai)) IN (8,10),REMOVEcaractere(telpai),if(length(REMOVEcaractere(celularpai)) IN (8,10),REMOVEcaractere(celularpai),NULL) ) AS telpai,
if(length(REMOVEcaractere(celularpai)) IN (9,11),REMOVEcaractere(celularpai),if(length(REMOVEcaractere(telpai)) IN (9,11),REMOVEcaractere(telpai),NULL)) AS celularpai,
if(validaemail(emailpai) = 'V', emailpai, NULL) AS emailpai,
UPPER(mae) AS mae,
UPPER(trabmae) AS trabmae,
UPPER(profmae) AS profmae,
if(length(REMOVEcaractere(telmae)) IN (8,10),REMOVEcaractere(telmae),if(length(REMOVEcaractere(celularmae)) IN (8,10),REMOVEcaractere(celularmae),NULL) ) AS telmae,
if(length(REMOVEcaractere(celularmae)) IN (9,11),REMOVEcaractere(celularmae),if(length(REMOVEcaractere(telmae)) IN (9,11),REMOVEcaractere(telmae),NULL)) AS celularmae,
if(validaemail(emailmae) = 'V', emailmae, NULL) AS emailmae,
UPPER(CONCAT(ifnull(obs,''),' / ORGÃO EMISSOR DO RG DO ALUNO: ',IFNULL(rgorgaoemissor,''),' / ORGÃO EMISSOR DO RG DO PAI: ',IFNULL(rgorgaoemissorpai,''),' / ORGÃO EMISSOR DO RG DA MÃE: ',IFNULL(rgorgaoemissormae,''),' / EMAIL ALUNO: ',IFNULL(email,''),' / EMAIL ´PAI: ',IFNULL(emailpai,''),' / EMAIL MAE: ',IFNULL(emailmae,''))) AS obs,
lpad(REMOVEcaractere(cpf),11,'0') AS cpf,
if(LENGTH(a.rgorgaoemissor) <=5 , CONCAT(ifnull(a.rg,''),'/',ifnull(a.rgorgaoemissor,'')), a.rg ) AS rg,
foto,
senha,
UPPER(nomeabrev) AS nomeabrev,
certidaonumero,
certidaolivrofolha,
certidaocartorio,
UPPER(religiao) AS religiao,
codeducacaoespecial,
obseducacaoespecial,
codsocio,
vivopai,
instrucaopai,
UPPER(enderecopai) AS enderecopai,
UPPER(bairropai) AS bairropai,
codcidadepai,
codufpai,
left(REMOVEcaractere(ceppai),8) AS ceppai,
vivomae,
UPPER(instrucaomae) AS instrucaomae,
UPPER(enderecomae) AS enderecomae,
UPPER(bairromae) AS bairromae,
left(REMOVEcaractere(cepmae),8) AS cepmae,
codufmae,
codcidademae,
UPPER(ativo) AS ativo,
fotoPortal,
foneportal,
emailportal,
titeleitor,
zona,
now() as dtcadastro,
usercadastro,
dtalteracao,
useralteracao,
UPPER(nacionalidade) AS nacionalidade,
bloqueado,
UPPER(obshistfundamental) AS obshistfundamental,
UPPER(obshistmedio) AS obshistmedio,
UPPER(estadocivilpai) AS estadocivilpai,
UPPER(estadocivilmae) AS estadocivilmae,
dtnascimentopai,
dtnascimentomae,
codpaisnaturalidade,
alunoadotado,
sabeadocao,
paisseparados,
tiposanguineo,
planosaude,
alergia,
doencacongenita,
deficiencia,
cuidadoespecial,
locomocaoida,
locomocaovolta,
obsguiatransferencia,
UPPER(naturalidade) AS naturalidade,
lpad(REMOVEcaractere(cpfpai),11,'0') AS cpfpai,
if(LENGTH(a.rgorgaoemissorpai) <=5 , CONCAT(ifnull(a.rgpai,''),'/',ifnull(a.rgorgaoemissorpai,'')), a.rgpai ) AS rgpai,
lpad(REMOVEcaractere(cpfmae),11,'0') AS cpfmae,
if(LENGTH(a.rgorgaoemissormae) <=5 , CONCAT(ifnull(a.rgmae,''),'/',ifnull(a.rgorgaoemissormae,'')), a.rgmae ) AS rgmae,
fotoblob,
UPPER(estadocivil) AS estadocivil,
saisozinho,
tipocertidao,
certreservista,
UPPER(profissao) AS profissao,
UPPER(localtrabalho) AS localtrabalho,
teltrabalho,
numcartao,
enviarsms,
celularsms,
operadorasms,
hospital,
pne,
pnedescricao,
pneavaliado,
alunofalecido,
codcenso,
codportaria,
codpaisorigem,
zonaresidencia7024,
NULL AS rgorgaoemissor,
rgea.cod_estado AS rgestado,
rgdataemissao,
rgcomplemento,
modelocertidao,
codcartoriocertidao,
dtemissaocertidao,
NULL AS rgorgaoemissorpai,
rgestadopai,
rgdataemissaopai,
upper(nacionalidadepai) AS nacionalidadepai,
codpaisorigempai,
codcidadenaturalidadepai,
codescolaridadepai,
NULL AS rgorgaoemissormae,
rgestadomae,
rgdataemissaomae,
UPPER(nacionalidademae) AS nacionalidade,
codpaisorigemmae,
codcidadenaturalidademae,
codescolaridademae,
codrendafamiliar,
UPPER(obsfinanceiro) AS obsfinanceiro,
UPPER(obsacademico) AS obsacademico,
sexomae,
sexopai,
codbolsapadrao,
diavencpadrao,
ra,
localbatalhaomilitar,
categoriamilitar,
situacaomilitar,
dataexpedicaomilitar,
secaoeleitor,
dataexpedicaoeleitor,
avatoken,
avauserid,
UPPER(obshistinfantil) AS obshistinfantil,
idgoogledrive,
codescolaridadealuno
FROM mig_aluno_siga a
LEFT JOIN estado rgea ON (a.rgestado = rgea.sgl_estado)
GROUP BY a.nomealuno);", conn);
insertAluno.ExecuteNonQuery();
MySqlCommand updates = new MySqlCommand($@"UPDATE mig_aluno_siga a
JOIN aluno aa ON a.nomealuno = aa.nomealuno
SET a.codaluno_tella = aa.codaluno;
-- Alunos com o cpf repetido
UPDATE aluno a
SET a.obs = CONCAT(ifnull(a.obs,''),' | CPF DO ALUNO: ',ifnull(a.cpf,'')),
a.cpf = NULL
WHERE a.cpf IN (SELECT tab.cpf FROM (SELECT aa.cpf from mig_aluno_siga aa
WHERE aa.cpf IS NOT NULL
GROUP BY aa.cpf HAVING COUNT(1)>1) AS tab )
AND LENGTH(CONCAT(ifnull(a.obs,''),' | CPF DO ALUNO: ',ifnull(a.cpf,''))) <= 600;
UPDATE aluno a
SET a.obsfinanceiro = CONCAT(ifnull(a.obsfinanceiro,''),' | CPF DO ALUNO: ',ifnull(a.cpf,'')),
a.cpf = NULL
WHERE a.cpf IN (SELECT tab.cpf FROM (SELECT aa.cpf from mig_aluno_siga aa
WHERE aa.cpf IS NOT NULL
GROUP BY aa.cpf HAVING COUNT(1)>1) AS tab )
AND LENGTH(CONCAT(ifnull(a.obsfinanceiro,''),' | CPF DO ALUNO: ',ifnull(a.cpf,''))) <= 600;
-- Alunos com o mesmo cpf da mãe
UPDATE aluno a
SET a.obs = CONCAT(IFNULL(a.obs,''),' | CPF DO ALUNO: ',IFNULL(a.cpf,'')),
a.cpf = NULL
WHERE a.cpf = a.cpfmae
AND a.cpf IS NOT NULL;
-- Alunos com o mesmo cpf do pai
UPDATE aluno a
SET a.obs = CONCAT(IFNULL(a.obs,''),' | CPF DO ALUNO: ',IFNULL(a.cpf,'')),
a.cpf = NULL
WHERE a.cpf = a.cpfpai
AND a.cpf IS NOT NULL;
-- Pai e mãe com o mesmo cpf
-- onde a mãe é o responsável
UPDATE aluno a
JOIN responsavel r ON (a.cpfmae = r.cpf)
SET a.obs = CONCAT(ifnull(a.obs,''),' | CPF DO PAI: ',a.cpfpai) ,
a.cpfpai = NULL
WHERE a.cpfpai = a.cpfmae
AND a.cpfmae IS NOT NULL
AND a.mae = r.nomeresponsavel ;
-- onde o pai é o responsável
UPDATE aluno a
JOIN responsavel r ON (a.cpfpai = r.cpf)
SET a.obs = CONCAT(ifnull(a.obs,''),' | CPF DA MÃE: ',a.cpfmae) ,
a.cpfmae = NULL
WHERE a.cpfpai = a.cpfmae
AND a.cpfpai IS NOT NULL
AND a.pai = r.nomeresponsavel ;
-- quando o nome de nenhum dos dois é igual o do responsável
UPDATE aluno a
SET a.obs = CONCAT(ifnull(a.obs,''),' | CPF DA MÃE: ',a.cpfmae,' | CPF DO PAI: ',a.cpfpai),
a.cpfmae = NULL,
a.cpfpai = NULL
WHERE a.cpfpai = a.cpfmae
AND a.cpfpai IS NOT NULL;", conn);
updates.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from aluno;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
FbCommand MySelect = new FbCommand(@"select
(COALESCE(sc.classe,'X') || COALESCE(sc.turno,'Y') || COALESCE(sc.periodo,'Z')) as dscturma
from sigaluno sc
group by (COALESCE(sc.classe,'X') || COALESCE(sc.turno,'Y') || COALESCE(sc.periodo,'Z'));", conn2);
DataTable dtable = new DataTable();
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(@"SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM turma;
INSERT INTO turma (dscturma) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["dscturma"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
DataTable dtable2 = new DataTable();
StringBuilder queryBuilder2 = new StringBuilder();
queryBuilder2.Append(@"create table turma_tella(
codturma int,
dscturma varchar(10),
turno char(10));");
FbCommand createTable = new FbCommand(queryBuilder2.ToString(), conn2);
createTable.ExecuteNonQuery();
MySqlCommand createTurma = new MySqlCommand(@"select codturma, dscturma, turno from turma;", conn);
MySqlDataAdapter adapter2 = new MySqlDataAdapter(createTurma);
adapter2.Fill(dtable2);
StringBuilder queryBuilder3 = new StringBuilder();
for (int i = 0; i < dtable2.Rows.Count; i++)
{
queryBuilder3.Append($@"insert into turma_tella (codturma, dscturma, turno) values ('{dtable2.Rows[i]["codturma"]}' ,'{dtable2.Rows[i]["dscturma"]}' , '{dtable2.Rows[i]["turno"]}');");
FbCommand insertTable = new FbCommand(queryBuilder3.ToString(), conn2);
insertTable.ExecuteNonQuery();
queryBuilder3.Clear();
}
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from turma;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
DataTable dtable = new DataTable();
FbCommand query = new FbCommand(@"select
right(sc.anoletivo,2)||lpad((sc.unidade || sc.curso || sc.serie),6,0)||lpad(t.codturma,4,0) as codconfturma_aux,
(sc.unidade || sc.curso || sc.serie) as codseriecurso,
sc.descricao as dscturma,
coalesce(EXTRACT(YEAR FROM(sc.datainicio )) || '-' || EXTRACT(MONTH FROM (sc.datainicio )) || '-' || EXTRACT(DAY FROM (sc.datainicio )), sc.anoletivo || '-02-05') dtinicio,
coalesce(EXTRACT(YEAR FROM(sc.datafim )) || '-' || EXTRACT(MONTH FROM (sc.datafim )) || '-' || EXTRACT(DAY FROM (sc.datafim )), sc.anoletivo || '-12-20') as dtfim,
sc.anoletivo as anoletivo,
sc.turno as turno,
coalesce(EXTRACT(YEAR FROM(current_timestamp)) || '-' || EXTRACT(MONTH FROM (current_timestamp)) || '-' || EXTRACT(DAY FROM (current_timestamp)), sc.anoletivo || '-12-20') as dtcadastro,
'MIGRACAO' as cadastradopor,
t.codturma as codturma
from sigclass sc
join turma_tella t on t.dscturma = (COALESCE(sc.classe,'X') || COALESCE(sc.turno,'Y') || COALESCE(sc.periodo,'Z'));", conn2);
FbDataAdapter adapter = new FbDataAdapter(query);
adapter.Fill(dtable);
StringBuilder query2 = new StringBuilder();
query2.Append("ALTER TABLE configturma ADD COLUMN codconfturma_aux VARCHAR(30);" +
"SET FOREIGN_KEY_CHECKS = 0; " +
"DELETE FROM configturma;" +
"INSERT INTO configturma (codconfturma_aux,codseriecurso,dscturma,dtinicio,dtfim,anoletivo,turno,dtcadastro,cadastradopor,codturma) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
query2.Append($@"('{dtable.Rows[i]["codconfturma_aux"]}' , '{dtable.Rows[i]["codseriecurso"]}' , '{dtable.Rows[i]["dscturma"]}' , '{dtable.Rows[i]["dtinicio"]}' , '{dtable.Rows[i]["dtfim"]}' , '{dtable.Rows[i]["anoletivo"]}' , '{dtable.Rows[i]["turno"]}' , '{dtable.Rows[i]["dtcadastro"]}' , '{dtable.Rows[i]["cadastradopor"]}' , '{dtable.Rows[i]["codturma"]}'), ");
}
query2.Remove(query2.Length - 2, 2);
MySqlCommand query3 = new MySqlCommand(query2.ToString(), conn);
query3.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
//para remover a coluna
log.CommandText = "ALTER TABLE configturma DROP COLUMN codconfturma_aux;";
log.ExecuteNonQuery();
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from configturma;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
DataTable dtable = new DataTable();
FbCommand MySelect = new FbCommand(@"SELECT (C.unidade || C.codigo) as codcurso,
max(C.descricao) as dsccurso,
max(C.resumo) as dscabreviada,
max(0) as cargahoraria,
max('S') as ativo,
max(IIF(c.tipo = 'L', 3, IIF(c.descricao like '%FUNDAM%', 1,2))) as codnivel,
1 as formulamediaetapa
FROM sigcurso C
group BY C.unidade, C.codigo;", conn2);
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("SET FOREIGN_KEY_CHECKS = 0; " +
"DELETE FROM curso;" +
"INSERT INTO curso (codcurso,dsccurso,dscabreviada,cargahoraria,ativo,codnivel,formulamediaetapa) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["codcurso"]}' , '{dtable.Rows[i]["dsccurso"]}' , '{dtable.Rows[i]["dscabreviada"]}' , '{dtable.Rows[i]["cargahoraria"]}' , '{dtable.Rows[i]["ativo"]}' , '{dtable.Rows[i]["codnivel"]}' , '{dtable.Rows[i]["formulamediaetapa"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from curso;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
MySqlCommand create = new MySqlCommand($@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_funcionario_siga;
DELETE FROM funcionariounidade;
DELETE FROM funcionario;
CREATE TABLE `mig_funcionario_siga` (
`FUNCODIGO` INT(11) NOT NULL AUTO_INCREMENT,
`CODESCOLARIDADE` INT(11) NULL DEFAULT NULL,
`CODBANCO` INT(11) NULL DEFAULT NULL,
`funbancoagencia` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`funbancoconta` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`funbancooperacao` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CODUF` INT(11) NULL DEFAULT NULL,
`NOME` VARCHAR(100) NOT NULL COLLATE 'latin1_swedish_ci',
`FOTO` VARCHAR(150) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`SEXO` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`DTNASCIMENTO` varchar(100) NULL DEFAULT NULL,
`NATURALIDADE` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`ESTADOCIVIL` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CONJUGE` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`DEPENDENTES` INT(11) NULL DEFAULT NULL,
`ESCOLARIDADE` INT(11) NULL DEFAULT NULL,
`ENDERECO` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`COMPLEMENTO` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`BAIRRO` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CIDADE` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CEP` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`FONERESID` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CELULAR` VARCHAR(11) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`FONRECADO` VARCHAR(11) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`EMAIL` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`RG` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CPF` VARCHAR(11) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CATRABALHO` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`SERIECTRABALHO` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`HABILITACAO` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CATEGORIA` VARCHAR(2) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`DTADMISSAO` DATE NULL DEFAULT NULL,
`DTDESLIGAMENTO` DATE NULL DEFAULT NULL,
`MOTIVO` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`OBSERVACOES` VARCHAR(2000) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`ATIVO` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CARTASSINADA` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CAPACETE` INT(11) NULL DEFAULT NULL,
`TITELEITOR` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`TITZONA` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`HORAENTRADA` varchar(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`HORASAIDA` varchar(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`DESCANSO` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`FGTSOPTANTE` CHAR(3) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`FGTSDTOPCAO` DATE NULL DEFAULT NULL,
`FGTSDTRETRATACAO` DATE NULL DEFAULT NULL,
`NACIONALIDADE` VARCHAR(23) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`PAI` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`MAE` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`NUNCTMILIT` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`SERIECTMILIT` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CATEGCTMILIT` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`PISDTCADASTRO` DATE NULL DEFAULT NULL,
`PISNUMERO` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`PISENDERECOBANCO` VARCHAR(150) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`PISCODBANCO` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`PISCODAGENCIA` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`PISOBS` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`DTENTRADA` DATE NULL DEFAULT NULL,
`LIVREGISTRO` INT(11) NULL DEFAULT NULL,
`RESERVA` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`CODCARGO` INT(11) NULL DEFAULT NULL,
`PISBANCO` INT(11) NULL DEFAULT NULL,
`FGTSBANCO` INT(11) NULL DEFAULT NULL,
`FOTOPORTAL` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`LOGIN` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`SENHA` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`EMAILPORTAL` VARCHAR(60) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`FONEPORTAL` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`bloqueado` CHAR(1) NULL DEFAULT 'N' COLLATE 'latin1_swedish_ci',
`UF` CHAR(2) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`horariobloqueio` DATETIME NULL DEFAULT NULL,
`ipbloqueio` varchar(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`loginbloqueio` varchar(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`localbloqueio` varchar(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`SENHAADM` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`fotoblob` BLOB NULL DEFAULT NULL,
`cor` varchar(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`codpaisorigem` SMALLINT(6) NULL DEFAULT NULL,
`codinep` VARCHAR(12) NULL DEFAULT NULL COMMENT 'codigo do profissional no INEP' COLLATE 'latin1_swedish_ci',
`codcidadenaturalidade` INT(11) NULL DEFAULT NULL,
`zonaresidencia4006` INT(1) NULL DEFAULT NULL COMMENT '1-Urbana/2-Rural',
`regimecontratacao` INT(1) NULL DEFAULT NULL COMMENT '1-Concursado / 2-Contrato temporario / 3-Contrato tercerizado / 4-Contrato CLT',
`numcartao` varchar(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`alcunha` VARCHAR(60) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`rf` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Registro Funcional do Professor da Prefeitura Municipal de São Paulo' COLLATE 'latin1_swedish_ci',
`qpe` VARCHAR(5) NULL DEFAULT NULL COMMENT 'evolução que o Professor possui e indicativo do que ele precisa fazer para evoluir' COLLATE 'latin1_swedish_ci',
`avatoken` VARCHAR(200) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`tokenfirebase` VARCHAR(256) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`chat_ativo` CHAR(1) NULL DEFAULT 'N' COLLATE 'latin1_swedish_ci',
`idgoogledrive` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`wakkeuserid` CHAR(36) NULL DEFAULT NULL COMMENT 'Identificador do funcionário na Wakke Class' COLLATE 'latin1_swedish_ci',
PRIMARY KEY (`FUNCODIGO`) USING BTREE,
UNIQUE INDEX `UK_funcionario_wakkeuserid` (`wakkeuserid`) USING BTREE,
INDEX `FK_CODESCOLARIDADE` (`CODESCOLARIDADE`) USING BTREE,
INDEX `funcionario_ibfk_1` (`CODCARGO`) USING BTREE)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;", conn);
create.ExecuteNonQuery();
DataTable dtable = new DataTable();
FbCommand MySelect = new FbCommand(@"
select
(p.unidade || p.codigo) as funcodigo,
replace(p.nome, '''', '') as nome,
replace(p.sexo, '''', '') as sexo,
replace(p.email, '''', '') as email,
-- p.datanasc as dtnascimento,
EXTRACT(YEAR FROM p.datanasc) || '-' || EXTRACT(MONTH FROM p.datanasc) || '-' || EXTRACT(DAY FROM p.datanasc) as dtnascimento,
replace(p.naturalidade, '''', '') as naturalidade,
replace((cast(p.endereco as varchar(100)) || cast(', ' as varchar(2)) || cast(COALESCE(p.numero, '') as varchar(10))), '''', '') as endereco,
replace(left(p.complemento, 10), '''', '') as complemento,
replace(p.bairro, '''', '') as bairro,
-- p.cidade as cidadeoriginal,
replace((select c.cod_cidade from cidade_tella c where c.nom_cidade like upper(p.cidade) and c.nom_estado = p.estado), '''', '') as cidade,
replace(replace(replace(replace(replace(replace(p.cep, '(', ''), '.', ''), '-', ''), '/', ''), ')', ''), '''', '') as cep,
1 as codcargo,
'S' as ativo
from sigprofe p
where p.unidade is not null; ", conn2);
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("INSERT INTO mig_funcionario_siga(funcodigo, nome, sexo, email, dtnascimento, naturalidade, endereco, complemento, bairro, cidade, cep, codcargo, ativo) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["FUNCODIGO"]}' , '{dtable.Rows[i]["nome"]}' , '{dtable.Rows[i]["sexo"]}' , '{dtable.Rows[i]["email"]}' , '{dtable.Rows[i]["dtnascimento"]}' , '{dtable.Rows[i]["naturalidade"]}' , '{dtable.Rows[i]["endereco"]}' , '{dtable.Rows[i]["complemento"]}' , '{dtable.Rows[i]["bairro"]}' , '{dtable.Rows[i]["cidade"]}' , '{dtable.Rows[i]["cep"]}' , '{dtable.Rows[i]["codcargo"]}' , '{dtable.Rows[i]["ativo"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
MySqlCommand MySelect2 = new MySqlCommand(@"SET FOREIGN_KEY_CHECKS = 0;
UPDATE mig_funcionario_siga SET nome = NULL WHERE nome = '';
UPDATE mig_funcionario_siga SET sexo = NULL WHERE sexo = '';
UPDATE mig_funcionario_siga SET email = NULL WHERE email = '';
UPDATE mig_funcionario_siga SET dtnascimento = NULL WHERE dtnascimento = '';
UPDATE mig_funcionario_siga SET naturalidade = NULL WHERE naturalidade = '';
UPDATE mig_funcionario_siga SET endereco = NULL WHERE endereco = '';
UPDATE mig_funcionario_siga SET complemento = NULL WHERE complemento = '';
UPDATE mig_funcionario_siga SET bairro = NULL WHERE bairro = '';
UPDATE mig_funcionario_siga SET cidade = NULL WHERE cidade = '';
UPDATE mig_funcionario_siga SET cep = NULL WHERE cep = '';
UPDATE mig_funcionario_siga SET codcargo = NULL WHERE codcargo = '';
UPDATE mig_funcionario_siga SET ativo = NULL WHERE ativo = '';
INSERT INTO funcionario (funcodigo, nome, sexo, email, dtnascimento,naturalidade, endereco, complemento, bairro, cidade, cep, codcargo, ativo)
SELECT funcodigo, nome, sexo, email, dtnascimento,naturalidade, endereco, complemento, bairro, cidade, cep, codcargo, ativo FROM mig_funcionario_siga fs GROUP BY fs.nome;
INSERT INTO funcionariounidade (codfuncionario,codunidade) (SELECT funcodigo, 1 FROM funcionario)" , conn);
MySelect2.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from funcionario;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
DataTable dtable = new DataTable();
try
{
FbCommand MySelect = new FbCommand(@"select u.unidade as codunidade,
u.nome as dscunidade,
u.fantasia as nomefantasia,
replace(replace(replace(replace(u.cnpj,'.',''),'-',''),'/',''),')','') as cnpj,
u.endereco as endereco,
u.bairro as bairro,
c.cod_cidade as cidade,
replace(replace(replace(replace(u.cep,'.',''),'-',''),'/',''),')','') as cep,
replace(replace(replace(replace(replace(replace(u.telefone,'(',''),'.',''),'-',''),'/',''),')',''),' ','') as telefone,
u.a_email as email,
left(u.nome,10) as dscunidadeabrev
from sigunida u
left join cidade_tella c on c.nom_cidade = UPPER(u.cidade) and c.sgl_estado = u.estado;", conn2);
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("SET foreign_key_checks = 0;" +
"DELETE FROM unidadeescolar;" +
"DELETE FROM organizacaoseriecurso;" +
"DELETE FROM cursosunidade;" +
"INSERT INTO unidadeescolar(codunidade,dscunidade,nomefantasia,cnpj,endereco,bairro,cidade,cep,telefone,email,dscunidadeabrev) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["codunidade"]}' , '{dtable.Rows[i]["dscunidade"]}' , '{dtable.Rows[i]["nomefantasia"]}' , '{dtable.Rows[i]["cnpj"]}' ,'{dtable.Rows[i]["endereco"]}' , '{dtable.Rows[i]["bairro"]}' , '{dtable.Rows[i]["cidade"]}' , '{dtable.Rows[i]["cep"]}' , '{dtable.Rows[i]["telefone"]}' , '{dtable.Rows[i]["email"]}' , '{dtable.Rows[i]["dscunidadeabrev"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
DataTable dtable2 = new DataTable();
FbCommand MySelect2 = new FbCommand(@"select
e.codigo as codunidadesiga,
e.descricao as dscunidade,
left(e.resumo,10) as dscunidadeabrev,
e.descricao as nomefantasia,
e.cnpj as cnpj,
e.endereco as endereco
from sigempre e;", conn2);
FbDataAdapter adapter2 = new FbDataAdapter(MySelect2);
adapter2.Fill(dtable2);
StringBuilder queryBuilder2 = new StringBuilder();
queryBuilder2.Append(@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_unidadeescolar_siga;
CREATE TABLE mig_unidadeescolar_siga (
codunidadesiga VARCHAR(100) NOT NULL,
dscunidade VARCHAR(100) NOT NULL,
dscunidadeabrev VARCHAR(100) NOT NULL,
nomefantasia VARCHAR(100) NOT NULL,
cnpj VARCHAR(100),
endereco VARCHAR(100));
ALTER TABLE unidadeescolar ADD COLUMN codunidadesiga INTEGER;
INSERT INTO mig_unidadeescolar_siga(codunidadesiga,dscunidade,dscunidadeabrev,nomefantasia,cnpj,endereco) VALUES " );
for (int i = 0; i < dtable2.Rows.Count; i++)
{
queryBuilder2.Append($@"('{dtable2.Rows[i]["codunidadesiga"]}' , '{dtable2.Rows[i]["dscunidade"]}' , '{dtable2.Rows[i]["dscunidadeabrev"]}' , '{dtable2.Rows[i]["nomefantasia"]}' ,'{dtable2.Rows[i]["cnpj"]}' , '{dtable2.Rows[i]["endereco"]}'), ");
}
queryBuilder2.Remove(queryBuilder2.Length - 2, 2);
MySqlCommand query2 = new MySqlCommand(queryBuilder2.ToString(), conn);
query2.ExecuteNonQuery();
DataTable dtable3 = new DataTable();
MySqlCommand MySelect3 = new MySqlCommand(@"SELECT codunidadesiga,dscunidade,dscunidadeabrev,nomefantasia,cnpj,endereco FROM mig_unidadeescolar_siga
WHERE cnpj NOT IN (SELECT cnpj FROM unidadeescolar)
GROUP BY cnpj;" , conn);
MySqlDataAdapter adapter3 = new MySqlDataAdapter(MySelect3);
adapter3.Fill(dtable3);
StringBuilder queryBuilder3 = new StringBuilder();
queryBuilder3.Append("INSERT INTO unidadeescolar(codunidadesiga,dscunidade,dscunidadeabrev,nomefantasia,cnpj,endereco) VALUES ");
for (int i = 0; i < dtable3.Rows.Count; i++)
{
queryBuilder3.Append($@"('{dtable3.Rows[i]["codunidadesiga"]}' , '{dtable3.Rows[i]["dscunidade"]}' , '{dtable3.Rows[i]["dscunidadeabrev"]}' , '{dtable3.Rows[i]["nomefantasia"]}' ,'{dtable3.Rows[i]["cnpj"]}' , '{dtable3.Rows[i]["endereco"]}'), ");
}
queryBuilder3.Remove(queryBuilder3.Length - 2, 2);
MySqlCommand query3 = new MySqlCommand(queryBuilder3.ToString(), conn);
query3.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
//para remover a coluna
log.CommandText = "ALTER TABLE unidadeescolar DROP COLUMN codunidadesiga;";
log.ExecuteNonQuery();
MessageBox.Show(err.Message);
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from unidadeescolar;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
int anoMenor = 0;
int anoMaior = 0;
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
ResetaValor.Resetar(out anoMenor, out anoMaior, FbConn);
while (anoMenor <= anoMaior)
{
MySqlCommand liquidacaoBoleto = new MySqlCommand($@"SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM liquidacaoboleto;
DELETE FROM pagamento;
DELETE FROM pagamentoforma;
-- LIQUIDACAOBOLETO
INSERT INTO liquidacaoboleto(codreceita ,valorpago ,dtpagamento ,codaluno ,anoletivo ,parcela ,conta ,vencimento ,nossonumero,
valorapagar ,desconto ,valorparcela, dtprocessamento ,codrec , usuario)
(
select c.codreceita, ifnull(c.valorpago,0), c.dtpagamento, c.codaluno, c.anoletivo, c.parcela,
c.codconta, c.vencimento, c.nossonumero,
c.valorparcela, (c.desconto) as desconto, c.valorparcela, c.dtpagamento, c.codrec, 'MIGRACAO'
from contasreceber c
where c.dtpagamento is not null
and c.anoletivo = {anoMenor});
-- pagamento
INSERT INTO pagamento(codpagamento ,dtpagamento ,dtregistro ,registradopor ,codunidaderec ,totalpago ,obs ,codaluno ,cpfresp)
select l.codliquidacao as codpagamento, l.dtpagamento, l.dtprocessamento , l.usuario, c.codunidade, l.valorpago, l.obspagamento, l.codaluno, c.cpf
from liquidacaoboleto l
join contasreceber c ON c.codrec = l.codrec
where l.codpagamento is null
and l.anoletivo = {anoMenor};
-- pagamentoforma
INSERT INTO pagamentoforma(codpagamento ,codconta ,valorpago ,codmeiopagamentoitem ,codforma)
select l.codliquidacao as codpagamento, ifnull(c.codconta,(SELECT max(c.codconta) FROM conta c WHERE c.dscconta = 'CONTA MIGRACAO')) AS 'codconta', l.valorpago, null,
(CASE l.formapagamento
WHEN 'DI' THEN 1
WHEN 'LI' THEN 2
WHEN 'CD' THEN 4
WHEN 'CC' THEN 5
ELSE 1 END) AS codforma
from liquidacaoboleto l
join contasreceber c ON c.codrec = l.codrec
where l.codpagamento is NULL
and l.anoletivo = {anoMenor};", conn);
liquidacaoBoleto.ExecuteNonQuery();
anoMenor++;
}
ResetaValor.Resetar(out anoMenor, out anoMaior, FbConn);
while (anoMenor <= anoMaior)
{
MySqlCommand update = new MySqlCommand($@"
-- seta codpagamento na liquidacaoboleto
update liquidacaoboleto l
set l.codpagamento = l.codliquidacao
where l.codpagamento is null
and l.anoletivo = {anoMenor};", conn);
update.ExecuteNonQuery();
anoMenor++;
}
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from liquidacaoboleto;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
DataTable dtable = new DataTable();
FbCommand MySelect = new FbCommand(@"select c.banco as codbanco,
c.nome as dscconta,
c.conta as numconta,
c.carteira as carteira,
c.sequencia as sequencialremessa,
c.agencia as agencia
from sigbccta c;", conn2);
//Crie um data adapter para trabalhar com datatables.
//Datatables são mais fáceis de manipular que utilizar o método Read()
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_conta_siga;
DELETE FROM conta;
CREATE TABLE mig_conta_siga (
CODBANCO VARCHAR(4),
DSCCONTA VARCHAR(42),
NUMCONTA VARCHAR(10),
CARTEIRA VARCHAR(3),
SEQUENCIALREMESSA varchar(100),
AGENCIA VARCHAR(5));" +
"INSERT INTO mig_conta_siga (codbanco,dscconta,numconta,carteira, sequencialremessa,agencia) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["codbanco"]}' , '{dtable.Rows[i]["dscconta"]}' , '{dtable.Rows[i]["numconta"]}' , '{dtable.Rows[i]["carteira"]}' , '{dtable.Rows[i]["sequencialremessa"]}' , '{dtable.Rows[i]["agencia"]}'), ");
}
//Remove a última vírgula da consulta, para evitar erros de sintaxe.
queryBuilder.Remove(queryBuilder.Length - 2, 2);
//O segredo da perfomace está aqui: Você somente executa a operação após construir toda a consulta.
//Antes você estava executando uma chamada no banco de dados a cada iteração do while. E isto é um pecado, em relação a perfomace =D
//var s = queryBuilder.ToString();
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
MySqlCommand update = new MySqlCommand(@$ "
UPDATE mig_conta_siga SET sequencialremessa = NULL where sequencialremessa = '';
UPDATE mig_conta_siga SET carteira = NULL where carteira = '';", conn);
update.ExecuteNonQuery();
MySqlCommand insert = new MySqlCommand(@$ "SET FOREIGN_KEY_CHECKS = 0; INSERT INTO conta (codbanco,dscconta,numconta,carteira,sequencialremessa,agencia)
(SELECT right(if(codbanco = '0356','033',codbanco),3) as codbanco,dscconta,numconta,carteira,sequencialremessa,agencia FROM mig_conta_siga);
INSERT INTO conta (dscconta) VALUES ('CONTA MIGRACAO');", conn); //usado no ifnull do contasreceber > insert pagamentoforma
insert.ExecuteNonQuery();
//para configurar o codempresa, é preciso ver com o cliente a qual EMPRESA a CONTA pertence
MessageBox.Show("Importação concluída com sucesso, agora, configure o codempresa da tabela conta");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from conta;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
FbCommand MySelect = new FbCommand(@"select
(cr.unidade || cr.codigo) as codreceita,
cr.nome as dscreceita
from sigparce cr;", conn2);
//Crie um data adapter para trabalhar com datatables.
//Datatables são mais fáceis de manipular que utilizar o método Read()
DataTable dtable = new DataTable();
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_centroreceita_siga;
DELETE FROM centroreceita;
create table mig_centroreceita_siga(
codreceita varchar(10),
dscreceita varchar(100),
codreceita_tella varchar(100),
INDEX `idx_codreceita` (`codreceita`));" +
"INSERT INTO mig_centroreceita_siga (codreceita,dscreceita) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["codreceita"]}' , '{dtable.Rows[i]["dscreceita"]}'), ");
}
//Remove a última vírgula da consulta, para evitar erros de sintaxe.
queryBuilder.Remove(queryBuilder.Length - 2, 2);
//O segredo da perfomace está aqui: Você somente executa a operação após construir toda a consulta.
//Antes você estava executando uma chamada no banco de dados a cada iteração do while. E isto é um pecado, em relação a perfomace =D
//var s = queryBuilder.ToString();
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
MySqlCommand insert = new MySqlCommand(@$ "SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO centroreceita (codreceita,dscreceita,ativo,multa,juros)
SELECT codreceita,left(dscreceita,20) dscreceita,'S' ativo,'0.02' multa,'0.00033' juros FROM mig_centroreceita_siga where codreceita > 0 GROUP BY dscreceita;
UPDATE mig_centroreceita_siga ca JOIN centroreceita c ON left(ca.dscreceita,20) = left(c.dscreceita,20) SET ca.codreceita_tella = c.codreceita;", conn);
insert.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from centroreceita;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
MySqlCommand mysql = new MySqlCommand("", conn);
FbCommand exportfb = new FbCommand("", conn2);
exportfb.CommandTimeout = 86400;
mysql.CommandTimeout = 86400;
DataTable dr = new DataTable();
mysql.CommandText = $@"SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS mig_contasreceber_siga;
DELETE FROM contasreceber;
CREATE TABLE `mig_contasreceber_siga` (
`codrec` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`codmatricula` varchar(100) NULL DEFAULT NULL,
`parcela` varchar(100) NULL DEFAULT NULL,
`totparcela` varchar(100) NULL DEFAULT NULL,
`valorparcela` varchar(100) NULL DEFAULT NULL,
`vencimento` varchar(100) NULL DEFAULT NULL,
`desconto` varchar(100) NULL DEFAULT NULL,
`carenciadesconto` varchar(100) NULL DEFAULT NULL,
`vencimentodesconto` varchar(100) NULL DEFAULT NULL,
`desconto2` varchar(100) NULL DEFAULT NULL,
`carenciadesconto2` varchar(100) NULL DEFAULT NULL,
`vencimentodesconto2` varchar(100) NULL DEFAULT NULL,
`multa` varchar(100) NULL DEFAULT NULL,
`juros` varchar(100) NULL DEFAULT NULL,
`carenciamulta` varchar(100) NULL DEFAULT NULL,
`vencimentomulta` varchar(100) NULL DEFAULT NULL,
`dtpagamento` varchar(100) NULL DEFAULT NULL,
`valorpago` varchar(100) NULL DEFAULT NULL,
`codunidade` varchar(100) NOT NULL,
`anoletivo` varchar(100) NOT NULL,
`codreceita` varchar(100) NULL DEFAULT NULL,
`cobbancaria` CHAR(1) NULL DEFAULT NULL,
`codaluno` varchar(100) NOT NULL,
`nossonumero` VARCHAR(100) NULL DEFAULT NULL,
`avulsa` CHAR(1) NULL DEFAULT NULL,
`cadastradopor` VARCHAR(100) NULL DEFAULT NULL,
`dtcadastro` varchar(100) NULL DEFAULT NULL,
`valorparcelaoriginal` varchar(100) NULL DEFAULT NULL,
`descontooriginal` varchar(100) NULL DEFAULT NULL,
`vencimentooriginal` varchar(100) NULL DEFAULT NULL,
`valorbruto` varchar(100) NULL DEFAULT NULL,
`descricao` VARCHAR(100) NULL DEFAULT NULL,
`cpf` VARCHAR(14) NULL DEFAULT NULL,
`receitaoriginal` varchar(100) NULL DEFAULT NULL,
`valor` varchar(100) NULL DEFAULT NULL,
`statusparcela` VARCHAR(20) NULL DEFAULT NULL,
`codfatura_aux` varchar(100) NULL DEFAULT NULL,
`agencia_aux` varchar(100) NULL DEFAULT NULL,
`conta_aux` varchar(100) NULL DEFAULT NULL,
PRIMARY KEY (`codrec`) USING BTREE,
INDEX `idx_cpf_aux` (`cpf`),
INDEX `idx_codaluno_aux` (`codaluno`),
INDEX `idx_agencia_aux` (`agencia_aux`),
INDEX `idx_conta_aux` (`conta_aux`),
INDEX `idx_anoletivo_aux` (`anoletivo`))
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;";
mysql.ExecuteNonQuery();
exportfb.CommandText = @"select
sa.codigo as codrec,
coalesce(max(cast(sa.codaluno as varchar(15))),null,'\N') as codmatricula,
coalesce(max(sa.numero),NULL,'\N') as parcela,
coalesce(cast(max(coalesce((select Max(iif(pp.parcelas = 0, 1, pp.parcelas)) from sigparce pp where pp.codigo = sa.tipo), 1)) as varchar(5)),NULL,'\N') as totparcela,
coalesce(replace(cast(max(sa.valortit) as varchar(15)),',', '.'),NULL,'\N') as valorparcela,
coalesce(max(EXTRACT(YEAR FROM sa.vencto1) || '-' || EXTRACT(MONTH FROM sa.vencto1) || '-' || EXTRACT(DAY FROM sa.vencto1)),NULL,'\N') as vencimento,
coalesce(replace(cast(max((sa.valortit - sa.valor1)) as varchar(15)),',', '.'),NULL,'\N') as desconto,
max(0) as carenciadesconto,
coalesce(max(EXTRACT(YEAR FROM sa.vencto1) || '-' || EXTRACT(MONTH FROM sa.vencto1) || '-' || EXTRACT(DAY FROM sa.vencto1)),NULL,'\N') as vencimentodesconto,
coalesce(replace(cast(max((sa.valortit - sa.valor2)) as varchar(15)),',', '.'),NULL,'\N') as desconto2,
max(0) as carenciadesconto2,
coalesce(max(EXTRACT(YEAR FROM sa.vencto2) || '-' || EXTRACT(MONTH FROM sa.vencto2) || '-' || EXTRACT(DAY FROM sa.vencto2)), NULL, '\N') as vencimentodesconto2,
coalesce(replace(cast(max(sa.multa) as varchar(15)),',', '.'),NULL,'\N') as multa,
coalesce(replace(cast(max(sa.jurosdia) as varchar(15)),',', '.'),NULL,'\N') as juros,
max(0) as carenciamulta,
coalesce(max(coalesce(EXTRACT(YEAR FROM sa.vencto2) || '-' || EXTRACT(MONTH FROM sa.vencto2) || '-' || EXTRACT(DAY FROM sa.vencto2)
, EXTRACT(YEAR FROM sa.vencto1) || '-' || EXTRACT(MONTH FROM sa.vencto1) || '-' || EXTRACT(DAY FROM sa.vencto1))),NULL,'\N') as vencimentomulta,
coalesce(max(iif(sp.estorno = 'S', NULL, EXTRACT(YEAR FROM sp.data) || '-' || EXTRACT(MONTH FROM sp.data) || '-' || EXTRACT(DAY FROM sp.data))),NULL,'\N') as dtpagamento,
coalesce(replace(cast(max((select sum(spp.valor) from sigpagtos spp where spp.codparcela = sa.codigo)) as varchar(15)),',', '.'),NULL,'\N') as valorpago,
coalesce(max(sa.unidade),NULL,'\N') as codunidade,
coalesce(max(sa.anoletivo),NULL,'\N') as anoletivo,
coalesce(max(sa.unidade || sa.tipo),NULL,'\N') as codreceita,
coalesce(max(iif(st.agencia is null, 'N', 'S')),NULL,'\N') as cobbancaria,
coalesce(max(replace(sa.prontuario, '-', '')),NULL,'\N') as codaluno,
coalesce(max(st.bolnossonumero),NULL,'\N') as nossonumero,
max('N') as avulsa,
coalesce(max('MIGRACAO'),NULL,'\N') as cadastradopor,
max(EXTRACT(YEAR FROM current_timestamp) || '-' || EXTRACT(MONTH FROM current_timestamp) || '-' || EXTRACT(DAY FROM current_timestamp)) as dtcadastro,
coalesce(replace(cast(max(sa.valor1) as varchar(15)),',', '.'),NULL,'\N') as valorparcelaoriginal,
coalesce(replace(cast(max((sa.valortit - sa.valor1)) as varchar(15)),',', '.'),NULL,'\N') as descontooriginal,
coalesce(max(EXTRACT(YEAR FROM sa.vencto1) || '-' || EXTRACT(MONTH FROM sa.vencto1) || '-' || EXTRACT(DAY FROM sa.vencto1)),NULL,'\N') as vencimentooriginal,
coalesce(replace(cast(max(sa.valoreal) as varchar(15)),',', '.'),NULL,'\N') as valorbruto,
coalesce(MAX(cr.nome || ' ' || extract(MONTH from sa.vencto1) || ' / ' || extract(year from sa.vencto1)),NULL,'\N') as descricao,
coalesce(max(coalesce(iif(m.cpfresp = '', '00000000000', replace(replace(m.cpfresp, '-', ''), '.', '')), '00000000000')),NULL,'\N') as cpf,
coalesce(max(sa.unidade || sa.tipo),NULL,'\N') as receitaoriginal,
coalesce(replace(cast(max(sa.valortit) as varchar(15)),',', '.'),NULL,'\N') as valor,
coalesce(max(sa.situacao),NULL,'\N') as statusparcela,
coalesce(max(sa.titulo),NULL,'\N') as codfatura_aux,
coalesce(max(st.agencia),NULL,'\N') as agencia_aux,
coalesce(max(st.conta),NULL,'\N') as conta_aux
from sigalucx sa
left
join sigtitulos st on st.codparcela = sa.codigo
left
join sigpagtos sp on sp.codparcela = sa.codigo
left
join sigaluno m on (m.codigo = sa.codaluno and m.unidade = sa.unidade )
left join sigparce cr on(cr.codigo = sa.tipo and cr.unidade = sa.unidade)
where sa.unidade = 01
and sa.codigo is not null
and coalesce(sa.situacao,'Q') not in ('G')-- Essa sigla 'G' é referente as parcelas deletadas.
group by sa.codigo;";
FbDataAdapter d = new FbDataAdapter(exportfb);
d.Fill(dr);
string local = $"C:\\Users\\{Environment.UserName}\\Desktop\\teste.txt";
using (StreamWriter s = new StreamWriter(local))
{
for (int i = 0; i < dr.Rows.Count; i++)
{
s.WriteLine($@"{dr.Rows[i]["CODREC"]},{dr.Rows[i]["CODMATRICULA"]},{dr.Rows[i]["PARCELA"]},{dr.Rows[i]["TOTPARCELA"]},{dr.Rows[i]["VALORPARCELA"]},{dr.Rows[i]["VENCIMENTO"]},{dr.Rows[i]["DESCONTO"]},{dr.Rows[i]["CARENCIADESCONTO"]},{dr.Rows[i]["VENCIMENTODESCONTO"]},{dr.Rows[i]["DESCONTO2"]},{dr.Rows[i]["CARENCIADESCONTO2"]},{dr.Rows[i]["VENCIMENTODESCONTO2"]},{dr.Rows[i]["MULTA"]},{dr.Rows[i]["JUROS"]},{dr.Rows[i]["CARENCIAMULTA"]},{dr.Rows[i]["VENCIMENTOMULTA"]},{dr.Rows[i]["DTPAGAMENTO"]},{dr.Rows[i]["VALORPAGO"]},{dr.Rows[i]["CODUNIDADE"]},{dr.Rows[i]["ANOLETIVO"]},{dr.Rows[i]["CODRECEITA"]},{dr.Rows[i]["COBBANCARIA"]},{dr.Rows[i]["CODALUNO"]},{dr.Rows[i]["NOSSONUMERO"]},{dr.Rows[i]["AVULSA"]},{dr.Rows[i]["CADASTRADOPOR"]},{dr.Rows[i]["DTCADASTRO"]},{dr.Rows[i]["VALORPARCELAORIGINAL"]},{dr.Rows[i]["DESCONTOORIGINAL"]},{dr.Rows[i]["VENCIMENTOORIGINAL"]},{dr.Rows[i]["VALORBRUTO"]},{dr.Rows[i]["DESCRICAO"]},{dr.Rows[i]["CPF"]},{dr.Rows[i]["RECEITAORIGINAL"]},{dr.Rows[i]["VALOR"]},{dr.Rows[i]["STATUSPARCELA"]},{dr.Rows[i]["CODFATURA_AUX"]},{dr.Rows[i]["AGENCIA_AUX"]},{dr.Rows[i]["CONTA_AUX"]};");
}
mysql.CommandText = $@"LOAD DATA INFILE 'C:/Users/{Environment.UserName}/Desktop/teste.txt' INTO TABLE mig_contasreceber_siga
FIELDS TERMINATED BY ','
LINES TERMINATED BY ';\r\n'";
mysql.ExecuteNonQuery();
s.Close();
File.Delete(local);
}
mysql.CommandText = $@"INSERT INTO contasreceber (CODREC, CODMATRICULA, PARCELA, TOTPARCELA, VALORPARCELA, VENCIMENTO, DESCONTO, CARENCIADESCONTO, VENCIMENTODESCONTO, DESCONTO2, CARENCIADESCONTO2, VENCIMENTODESCONTO2, MULTA, JUROS, CARENCIAMULTA, VENCIMENTOMULTA, DTPAGAMENTO, VALORPAGO, CODUNIDADE, ANOLETIVO, CODRECEITA, COBBANCARIA, CODALUNO, NOSSONUMERO, AVULSA, CADASTRADOPOR, DTCADASTRO, VALORPARCELAORIGINAL, DESCONTOORIGINAL, VENCIMENTOORIGINAL, VALORBRUTO, DESCRICAO, CPF, RECEITAORIGINAL, VALOR, STATUSPARCELA, CODFATURA_AUX, AGENCIA_AUX, CONTA_AUX)
(SELECT CODREC, CODMATRICULA, PARCELA, TOTPARCELA, VALORPARCELA, VENCIMENTO, DESCONTO, CARENCIADESCONTO, VENCIMENTODESCONTO, DESCONTO2, CARENCIADESCONTO2, VENCIMENTODESCONTO2, MULTA, JUROS, CARENCIAMULTA, VENCIMENTOMULTA, DTPAGAMENTO, VALORPAGO, CODUNIDADE, ANOLETIVO, CODRECEITA, COBBANCARIA, CODALUNO, NOSSONUMERO, AVULSA, CADASTRADOPOR, DTCADASTRO, VALORPARCELAORIGINAL, DESCONTOORIGINAL, VENCIMENTOORIGINAL, VALORBRUTO, DESCRICAO, CPF, RECEITAORIGINAL, VALOR, STATUSPARCELA, CODFATURA_AUX, AGENCIA_AUX, CONTA_AUX
FROM mig_contasreceber_siga c
WHERE c.codaluno IN (SELECT a.codaluno FROM aluno a WHERE a.codaluno = c.codaluno))";
mysql.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from contasreceber;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
MySqlCommand create = new MySqlCommand(@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_responsavel_siga;
DELETE FROM responsavel;
CREATE TABLE `mig_responsavel_siga` (
`cpf` VARCHAR(14) NOT NULL,
`nomeresponsavel` VARCHAR(100) NOT NULL,
`profissao` VARCHAR(40) NULL DEFAULT NULL,
`localtrabalho` VARCHAR(100) NULL DEFAULT NULL,
`teltrabalho` VARCHAR(11) NULL DEFAULT NULL,
`telresid` VARCHAR(11) NULL DEFAULT NULL,
`celular` VARCHAR(16) NULL DEFAULT NULL,
`email` VARCHAR(100) NULL DEFAULT NULL,
`rg` varchar(100) NULL DEFAULT NULL,
`endereco` VARCHAR(120) NULL DEFAULT NULL,
`bairro` VARCHAR(60) NULL DEFAULT NULL,
`cidade` varchar(100) NULL DEFAULT NULL,
`uf` INT(10) UNSIGNED NULL DEFAULT NULL,
`cep` VARCHAR(8) NULL DEFAULT NULL,
`rendaliquida` DECIMAL(10,2) NULL DEFAULT NULL,
`sexo` CHAR(1) NULL DEFAULT NULL,
`estadocivil` varchar(100) NULL DEFAULT NULL,
`dtnascimento` varchar(100) NULL DEFAULT NULL,
`escolaridade` VARCHAR(40) NULL DEFAULT NULL,
`pai` VARCHAR(100) NULL DEFAULT NULL,
`mae` VARCHAR(100) NULL DEFAULT NULL,
`enderecocom` VARCHAR(120) NULL DEFAULT NULL,
`bairrocom` VARCHAR(30) NULL DEFAULT NULL,
`cepcom` VARCHAR(8) NULL DEFAULT NULL,
`codcidadecom` INT(11) NULL DEFAULT NULL,
`cargo` VARCHAR(30) NULL DEFAULT NULL,
`senha` VARCHAR(15) NULL DEFAULT NULL,
`login` varchar(100) NULL DEFAULT NULL,
`fotoportal` VARCHAR(120) NULL DEFAULT NULL,
`nacionalidade` varchar(100) NULL DEFAULT NULL,
`naturalidade` varchar(100) NULL DEFAULT NULL,
`obs` VARCHAR(200) NULL DEFAULT NULL,
`rgorgaoemissor` INT(2) NULL DEFAULT NULL COMMENT 'Orgao Emissor da indentidade. Relaciona com a tabela orgaoemissoridentidade',
`rgestado` SMALLINT(6) NULL DEFAULT NULL COMMENT 'Estado Identidade',
`rgdataemissao` DATE NULL DEFAULT NULL COMMENT 'Data de Emissao RG',
`codpaisorigem` SMALLINT(6) NULL DEFAULT NULL,
`codcidadenaturalidade` INT(11) NULL DEFAULT NULL,
`codescolaridade` INT(11) NULL DEFAULT NULL,
`codfatura` INT(11) NULL DEFAULT NULL,
`agrupamentofatura` CHAR(1) NULL DEFAULT 'R' COMMENT 'R=responsavel; A=aluno;',
`placa` varchar(100) NULL DEFAULT NULL,
`veiculo` varchar(100) NULL DEFAULT NULL,
`fotoblob` BLOB NULL,
`dtcadastro` varchar(100) NULL DEFAULT NULL,
`dtalteracao` varchar(100) NULL DEFAULT NULL,
`cadastradopor` VARCHAR(30) NULL DEFAULT NULL,
`alteradopor` VARCHAR(30) NULL DEFAULT NULL,
`codbanco` INT(11) NULL DEFAULT NULL,
`agencia` VARCHAR(5) NULL DEFAULT NULL,
`conta` VARCHAR(10) NULL DEFAULT NULL,
`debitoautomaticoativo` CHAR(1) NULL DEFAULT 'N',
`clienteiugu` VARCHAR(40) NULL DEFAULT NULL COMMENT 'Id do responsavel no cadastro de clientes no iugu',
`inscricaoestadual` varchar(100) NULL DEFAULT NULL COMMENT 'Inscrição Estadual da empresa',
`tokenfirebase` VARCHAR(256) NULL DEFAULT NULL)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;", conn);
create.ExecuteNonQuery();
FbCommand respacademico = new FbCommand(@"
select
max(replace(replace(a.cpf_rp ,'.',''),'-','')) as cpf ,
replace(upper(max(a.resp_ped)), '''', '') as nomeresponsavel,
left(replace(replace(upper(max(a.profi_rp)),';',','), '\', ''),40) as profissao,
max(iif(char_length(left(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.foner_rp,'.',''),'-',''),'/',''),' ',''),'_',''),';',''),')',''),'(',''), ' ', ''),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'r',''),'s',''),'t',''),'u',''),'x',''),'z',''),'w',''),'y',''),'|',''),'\',''),',',''),'$',''),'!',''),'@',''),'+',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'R',''),'S',''),'T',''),'U',''),'V',''),'X',''),'Z',''),'W',''),'Y',''),'á',''),'à',''),'ã',''),'â',''),'Á',''),'À',''),'Ã',''),'Â',''),'é',''),'è',''),'ê',''),'É',''),'È',''),'Ê',''),'í',''),'ì',''),'î',''),'Í',''),'Ì',''),'Î',''),'ó',''),'ò',''),'ô',''),'õ',''),'Ó',''),'Ò',''),'Ô',''),'Õ',''),'ú',''),'ù',''),'û',''),'Ú',''),'Ù',''),'Û',''),'v',''),'q',''),'Q',''),10)) < 10, '', left(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.foner_rp,'.',''),'-',''),'/',''),' ',''),'_',''),';',''),')',''),'(',''), ' ', ''),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'r',''),'s',''),'t',''),'u',''),'x',''),'z',''),'w',''),'y',''),'|',''),'\',''),',',''),'$',''),'!',''),'@',''),'+',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'R',''),'S',''),'T',''),'U',''),'V',''),'X',''),'Z',''),'W',''),'Y',''),'á',''),'à',''),'ã',''),'â',''),'Á',''),'À',''),'Ã',''),'Â',''),'é',''),'è',''),'ê',''),'É',''),'È',''),'Ê',''),'í',''),'ì',''),'î',''),'Í',''),'Ì',''),'Î',''),'ó',''),'ò',''),'ô',''),'õ',''),'Ó',''),'Ò',''),'Ô',''),'Õ',''),'ú',''),'ù',''),'û',''),'Ú',''),'Ù',''),'Û',''),'v',''),'q',''),'Q',''),10))) as telresid,
max(iif(char_length(left(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.fonecelrp,'.',''),'-',''),'/',''),' ',''),'_',''),';',''),')',''),'(',''), ' ', ''),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'r',''),'s',''),'t',''),'u',''),'x',''),'z',''),'w',''),'y',''),'|',''),'\',''),',',''),'$',''),'!',''),'@',''),'+',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'R',''),'S',''),'T',''),'U',''),'V',''),'X',''),'Z',''),'W',''),'Y',''),'á',''),'à',''),'ã',''),'â',''),'Á',''),'À',''),'Ã',''),'Â',''),'é',''),'è',''),'ê',''),'É',''),'È',''),'Ê',''),'í',''),'ì',''),'î',''),'Í',''),'Ì',''),'Î',''),'ó',''),'ò',''),'ô',''),'õ',''),'Ó',''),'Ò',''),'Ô',''),'Õ',''),'ú',''),'ù',''),'û',''),'Ú',''),'Ù',''),'Û',''),'v',''),'q',''),'Q',''),11)) < 11, '', left(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.fonecelrp,'.',''),'-',''),'/',''),' ',''),'_',''),';',''),')',''),'(',''), ' ', ''),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'r',''),'s',''),'t',''),'u',''),'x',''),'z',''),'w',''),'y',''),'|',''),'\',''),',',''),'$',''),'!',''),'@',''),'+',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'R',''),'S',''),'T',''),'U',''),'V',''),'X',''),'Z',''),'W',''),'Y',''),'á',''),'à',''),'ã',''),'â',''),'Á',''),'À',''),'Ã',''),'Â',''),'é',''),'è',''),'ê',''),'É',''),'È',''),'Ê',''),'í',''),'ì',''),'î',''),'Í',''),'Ì',''),'Î',''),'ó',''),'ò',''),'ô',''),'õ',''),'Ó',''),'Ò',''),'Ô',''),'Õ',''),'ú',''),'ù',''),'û',''),'Ú',''),'Ù',''),'Û',''),'v',''),'q',''),'Q',''),11))) as celular,
max(a.email_rp) as email,
max(a.rg_rp) as rg,
replace(replace(upper(max(coalesce(a.ender_rp,'') || ' ' || coalesce(a.compl_rp,''))),';',','), '''', '') as endereco,
replace(replace(upper(max(a.bairr_rp)),';',','), '''', '') as bairro,
max(c.cod_cidade) as cidade,
max(left(replace(a.cep_rp,'-',''),8)) as cep,
max(a.estcivrp) as estadocivil,
max(EXTRACT(YEAR FROM a.nascresp_p) || '-' || EXTRACT(MONTH FROM a.nascresp_p) || '-' || EXTRACT(DAY FROM a.nascresp_p)) as dtnascimento,
max('BRASILEIRA') as nacionalidade,
max(EXTRACT(YEAR FROM current_timestamp) || '-' || EXTRACT(MONTH FROM current_timestamp) || '-' || EXTRACT(DAY FROM current_timestamp)) as dtcadastro,
max('MIGRACAO') as cadastradopor
from sigaluno a
left join cidade_tella c on c.nom_cidade = upper(a.cidade_rp)
where a.cpf_rp is not null
and a.cpf_rp not in ('')
group by a.cpf_rp ;", conn2);
DataTable dtablerespacad = new DataTable();
FbDataAdapter adapterrespacad = new FbDataAdapter(respacademico);
adapterrespacad.Fill(dtablerespacad);
StringBuilder queryBuilderrespacad = new StringBuilder();
queryBuilderrespacad.Append("INSERT INTO mig_responsavel_siga (cpf,nomeresponsavel,profissao,telresid,celular,rg,endereco,bairro,cidade,cep,estadocivil,dtnascimento,nacionalidade,dtcadastro,cadastradopor) VALUES ");
for (int i = 0; i < dtablerespacad.Rows.Count; i++)
{
queryBuilderrespacad.Append($@"('{dtablerespacad.Rows[i]["cpf"]}' , '{dtablerespacad.Rows[i]["nomeresponsavel"]}' , '{dtablerespacad.Rows[i]["profissao"]}' , '{dtablerespacad.Rows[i]["telresid"]}' , '{dtablerespacad.Rows[i]["celular"]}' , '{dtablerespacad.Rows[i]["rg"]}' , '{dtablerespacad.Rows[i]["endereco"]}' , '{dtablerespacad.Rows[i]["bairro"]}' , '{dtablerespacad.Rows[i]["cidade"]}' , '{dtablerespacad.Rows[i]["cep"]}' , '{dtablerespacad.Rows[i]["estadocivil"]}' , '{dtablerespacad.Rows[i]["dtnascimento"]}' , '{dtablerespacad.Rows[i]["nacionalidade"]}' , '{dtablerespacad.Rows[i]["dtcadastro"]}' , '{dtablerespacad.Rows[i]["cadastradopor"]}'), ");
}
queryBuilderrespacad.Remove(queryBuilderrespacad.Length - 2, 2);
MySqlCommand queryrespacad = new MySqlCommand(queryBuilderrespacad.ToString(), conn);
queryrespacad.ExecuteNonQuery();
FbCommand respfinanceiro = new FbCommand(@"
select
max(replace(replace(a.cpfresp ,'.',''),'-','')) as cpf ,
replace(upper(max(a.pagtoresp)),'''','') as nomeresponsavel,
replace(left(replace(upper(max(a.profiresp)),';',','),40),'''','') as profissao,
max(iif(char_length(left(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.foneresp,'.',''),'-',''),'/',''),' ',''),'_',''),';',''),')',''),'(',''), ' ', ''),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'r',''),'s',''),'t',''),'u',''),'x',''),'z',''),'w',''),'y',''),'|',''),'\',''),',',''),'$',''),'!',''),'@',''),'+',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'R',''),'S',''),'T',''),'U',''),'V',''),'X',''),'Z',''),'W',''),'Y',''),'á',''),'à',''),'ã',''),'â',''),'Á',''),'À',''),'Ã',''),'Â',''),'é',''),'è',''),'ê',''),'É',''),'È',''),'Ê',''),'í',''),'ì',''),'î',''),'Í',''),'Ì',''),'Î',''),'ó',''),'ò',''),'ô',''),'õ',''),'Ó',''),'Ò',''),'Ô',''),'Õ',''),'ú',''),'ù',''),'û',''),'Ú',''),'Ù',''),'Û',''),'v',''),'q',''),'Q',''),10)) < 10, '', left(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.foner_rp,'.',''),'-',''),'/',''),' ',''),'_',''),';',''),')',''),'(',''), ' ', ''),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'r',''),'s',''),'t',''),'u',''),'x',''),'z',''),'w',''),'y',''),'|',''),'\',''),',',''),'$',''),'!',''),'@',''),'+',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'R',''),'S',''),'T',''),'U',''),'V',''),'X',''),'Z',''),'W',''),'Y',''),'á',''),'à',''),'ã',''),'â',''),'Á',''),'À',''),'Ã',''),'Â',''),'é',''),'è',''),'ê',''),'É',''),'È',''),'Ê',''),'í',''),'ì',''),'î',''),'Í',''),'Ì',''),'Î',''),'ó',''),'ò',''),'ô',''),'õ',''),'Ó',''),'Ò',''),'Ô',''),'Õ',''),'ú',''),'ù',''),'û',''),'Ú',''),'Ù',''),'Û',''),'v',''),'q',''),'Q',''),10))) as telresid,
max(iif(char_length(left(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.fonecelresp,'.',''),'-',''),'/',''),' ',''),'_',''),';',''),')',''),'(',''), ' ', ''),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'r',''),'s',''),'t',''),'u',''),'x',''),'z',''),'w',''),'y',''),'|',''),'\',''),',',''),'$',''),'!',''),'@',''),'+',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'R',''),'S',''),'T',''),'U',''),'V',''),'X',''),'Z',''),'W',''),'Y',''),'á',''),'à',''),'ã',''),'â',''),'Á',''),'À',''),'Ã',''),'Â',''),'é',''),'è',''),'ê',''),'É',''),'È',''),'Ê',''),'í',''),'ì',''),'î',''),'Í',''),'Ì',''),'Î',''),'ó',''),'ò',''),'ô',''),'õ',''),'Ó',''),'Ò',''),'Ô',''),'Õ',''),'ú',''),'ù',''),'û',''),'Ú',''),'Ù',''),'Û',''),'v',''),'q',''),'Q',''),11)) < 11, '', left(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.fonecelrp,'.',''),'-',''),'/',''),' ',''),'_',''),';',''),')',''),'(',''), ' ', ''),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'r',''),'s',''),'t',''),'u',''),'x',''),'z',''),'w',''),'y',''),'|',''),'\',''),',',''),'$',''),'!',''),'@',''),'+',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'R',''),'S',''),'T',''),'U',''),'V',''),'X',''),'Z',''),'W',''),'Y',''),'á',''),'à',''),'ã',''),'â',''),'Á',''),'À',''),'Ã',''),'Â',''),'é',''),'è',''),'ê',''),'É',''),'È',''),'Ê',''),'í',''),'ì',''),'î',''),'Í',''),'Ì',''),'Î',''),'ó',''),'ò',''),'ô',''),'õ',''),'Ó',''),'Ò',''),'Ô',''),'Õ',''),'ú',''),'ù',''),'û',''),'Ú',''),'Ù',''),'Û',''),'v',''),'q',''),'Q',''),11))) as celular,
max(a.emailresp) as email,
max(a.rgresp) as rg,
replace(replace(upper(max(coalesce(a.endresp,'') || ' ' || coalesce(a.comresp,''))),';',','),'''','') as endereco,
replace(replace(upper(max(a.bairesp)),';',','), '''', '') as bairro,
max(c.cod_cidade) as cidade,
max(left(replace(a.cepresp,'-',''),8)) as cep,
max(a.estcivresp) as estadocivil,
max(EXTRACT(YEAR FROM a.datnasresp) || '-' || EXTRACT(MONTH FROM a.datnasresp) || '-' || EXTRACT(DAY FROM a.datnasresp)) as dtnascimento,
max('BRASILEIRA') as nacionalidade,
max(EXTRACT(YEAR FROM current_timestamp) || '-' || EXTRACT(MONTH FROM current_timestamp) || '-' || EXTRACT(DAY FROM current_timestamp)) as dtcadastro,
max('MIGRACAO') as cadastradopor
from sigaluno a
left join cidade_tella c on c.nom_cidade = upper(a.cidresp)
where a.cpfresp is not null
and a.cpfresp not in ('')
group by a.cpfresp;", conn2);
DataTable dtablerespfin = new DataTable();
FbDataAdapter adapterrespfin = new FbDataAdapter(respfinanceiro);
adapterrespfin.Fill(dtablerespfin);
StringBuilder queryBuilderrespfin = new StringBuilder();
queryBuilderrespfin.Append("INSERT INTO mig_responsavel_siga (cpf,nomeresponsavel,profissao,telresid,celular,rg,endereco,bairro,cidade,cep,estadocivil,dtnascimento,nacionalidade,dtcadastro,cadastradopor) VALUES ");
for (int i = 0; i < dtablerespfin.Rows.Count; i++)
{
queryBuilderrespfin.Append($@"('{dtablerespfin.Rows[i]["cpf"]}' , '{dtablerespfin.Rows[i]["nomeresponsavel"]}' , '{dtablerespfin.Rows[i]["profissao"]}' , '{dtablerespfin.Rows[i]["telresid"]}' , '{dtablerespfin.Rows[i]["celular"]}' , '{dtablerespfin.Rows[i]["rg"]}' , '{dtablerespfin.Rows[i]["endereco"]}' , '{dtablerespfin.Rows[i]["bairro"]}' , '{dtablerespfin.Rows[i]["cidade"]}' , '{dtablerespfin.Rows[i]["cep"]}' , '{dtablerespfin.Rows[i]["estadocivil"]}' , '{dtablerespfin.Rows[i]["dtnascimento"]}' , '{dtablerespfin.Rows[i]["nacionalidade"]}' , '{dtablerespfin.Rows[i]["dtcadastro"]}' , '{dtablerespfin.Rows[i]["cadastradopor"]}'), ");
}
queryBuilderrespfin.Remove(queryBuilderrespfin.Length - 2, 2);
MySqlCommand queryrespfin = new MySqlCommand(queryBuilderrespfin.ToString(), conn);
queryrespfin.ExecuteNonQuery();
//responsaveis que nao entraram na query de cima
DataTable dtable = new DataTable();
FbCommand MySelect = new FbCommand(@"select
s.cpfresp as cpf ,
replace(replace(replace(s.pagtoresp,'''',''),'\',''),';','') as nomeresponsavel,
replace(replace(replace(s.profiresp,'''',''),'\',''),';','') as profissao,
replace(s.foneresp,';','|') as telresid,
replace(s.fonecelresp,';','|') as celular,
s.emailresp as email,
s.rgresp as rg,
replace(coalesce(s.endresp,'') || ' ' || coalesce(s.comresp,''), '''', '') as endereco,
replace(s.bairesp, '''', '') as bairro,
5213 as cidade,
left(replace(s.cepresp,'-',''),8) as cep,
replace(replace(replace(s.estcivresp,'''',''),'\',''),';','') as estadocivil,
EXTRACT(YEAR FROM s.nascresp_p) || '-' || EXTRACT(MONTH FROM s.nascresp_p) || '-' || EXTRACT(DAY FROM s.nascresp_p) as dtnascimento,
'BRASILEIRA' as nacionalidade,
EXTRACT(YEAR FROM current_timestamp) || '-' || EXTRACT(MONTH FROM current_timestamp) || '-' || EXTRACT(DAY FROM current_timestamp) as dtcadastro,
'MIGRACAO' as cadastradopor
from siggeral s
where s.cpfresp in ('');", conn2);
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("INSERT INTO mig_responsavel_siga (cpf,nomeresponsavel,profissao,telresid,celular,email,rg,endereco,bairro,cidade,cep,estadocivil,dtnascimento,nacionalidade,dtcadastro,cadastradopor) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["cpf"]}' , '{dtable.Rows[i]["nomeresponsavel"]}' , '{dtable.Rows[i]["profissao"]}' , '{dtable.Rows[i]["telresid"]}' , '{dtable.Rows[i]["celular"]}' , '{dtable.Rows[i]["email"]}' , '{dtable.Rows[i]["rg"]}' , '{dtable.Rows[i]["endereco"]}' , '{dtable.Rows[i]["bairro"]}' , '{dtable.Rows[i]["cidade"]}' , '{dtable.Rows[i]["cep"]}' , '{dtable.Rows[i]["estadocivil"]}' , '{dtable.Rows[i]["dtnascimento"]}' , '{dtable.Rows[i]["nacionalidade"]}' , '{dtable.Rows[i]["dtcadastro"]}' , '{dtable.Rows[i]["cadastradopor"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
MySqlCommand update = new MySqlCommand(@$ "UPDATE mig_responsavel_siga r
SET r.cpf = LPAD(r.cpf,11,'0')
WHERE length(r.cpf) < 11;
UPDATE mig_responsavel_siga set dtnascimento = NULL where dtnascimento = '0';
UPDATE mig_responsavel_siga set dtcadastro = NULL where dtcadastro = '0';
update mig_responsavel_siga set dtnascimento = null where dtnascimento = '';
update mig_responsavel_siga set dtcadastro = null where dtcadastro = '';
update mig_responsavel_siga set dtalteracao = null where dtalteracao = '';
update mig_responsavel_siga set cidade = null where cidade = '';", conn);
update.ExecuteNonQuery();
MySqlCommand execute = new MySqlCommand($@" INSERT INTO responsavel (cpf, nomeresponsavel, profissao, localtrabalho, teltrabalho, telresid, celular, email, rg, endereco, bairro, cidade, uf, cep, rendaliquida, sexo, estadocivil, dtnascimento, escolaridade, pai, mae, enderecocom, bairrocom, cepcom, codcidadecom, cargo, senha, login, fotoportal, nacionalidade, naturalidade, obs, rgorgaoemissor, rgestado, rgdataemissao, codpaisorigem, codcidadenaturalidade, codescolaridade, codfatura, agrupamentofatura, placa, veiculo, fotoblob, dtcadastro, dtalteracao, cadastradopor, alteradopor, codbanco, agencia, conta, debitoautomaticoativo, clienteiugu, inscricaoestadual, tokenfirebase )
(SELECT cpf, nomeresponsavel, profissao, localtrabalho, teltrabalho, telresid, celular, email, rg, endereco, bairro, cidade, uf, cep, rendaliquida, sexo, estadocivil, dtnascimento, escolaridade, pai, mae, enderecocom, bairrocom, cepcom, codcidadecom, cargo, senha, login, fotoportal, nacionalidade, naturalidade, obs, rgorgaoemissor, rgestado, rgdataemissao, codpaisorigem, codcidadenaturalidade, codescolaridade, codfatura, agrupamentofatura, placa, veiculo, fotoblob, dtcadastro, dtalteracao, cadastradopor, alteradopor, codbanco, agencia, conta, debitoautomaticoativo, clienteiugu, inscricaoestadual, tokenfirebase
FROM mig_responsavel_siga r
WHERE r.cpf NOT LIKE '%0%'
GROUP BY r.cpf
);", conn);
execute.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from responsavel;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
MySqlCommand insert = new MySqlCommand("", conn);
insert.CommandTimeout = 86400;
try
{
MySqlCommand create = new MySqlCommand(@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_matricula_siga;
DELETE FROM matricula;
DELETE FROM login;
CREATE TABLE `mig_matricula_siga` (
`codmatricula` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`codaluno` INT(10) UNSIGNED NOT NULL,
`codseriecurso` INT(11) NULL DEFAULT NULL,
`codturma` INT(10) UNSIGNED NOT NULL,
`dtmatricula` varchar(100) NULL DEFAULT NULL,
`anoletivo` INT(10) UNSIGNED NULL DEFAULT NULL,
`dtinicio` varchar(100) NULL DEFAULT NULL,
`dtfim` varchar(100) NULL DEFAULT NULL,
`cpfrespacademico` VARCHAR(14) NULL DEFAULT NULL,
`cpfrespfinanceiro` VARCHAR(14) NULL DEFAULT NULL,
`parentescorespacademico` varchar(100) NULL DEFAULT NULL,
`parentescorespfinanceiro` varchar(100) NULL DEFAULT NULL,
`rematricula` CHAR(1) NULL DEFAULT NULL,
`situacao` CHAR(1) NULL DEFAULT 'C',
`dtcadastro` varchar(100) NULL DEFAULT NULL,
`cadastradopor` INT(10) UNSIGNED NULL DEFAULT NULL,
`qtdparcela` INT(10) UNSIGNED NULL DEFAULT NULL,
`primeirovenc` DATE NULL DEFAULT NULL,
`valoranuidade` DECIMAL(15,2) NULL DEFAULT NULL,
`codescolaorigem` INT(11) NULL DEFAULT NULL,
`dtalteracao` varchar(100) NULL DEFAULT NULL,
`alteradopor` INT(11) NULL DEFAULT NULL,
`desconto` DECIMAL(5,2) NULL DEFAULT NULL,
`bolsista` CHAR(1) NULL DEFAULT 'N',
`codbolsa` INT(11) NULL DEFAULT NULL,
`alunorespacademico` CHAR(1) NULL DEFAULT 'N',
`alunorespfinanceiro` CHAR(1) NULL DEFAULT 'N',
`valordiscipadicional` DECIMAL(15,2) NULL DEFAULT NULL,
`dtTransferencia` varchar(100) NULL DEFAULT NULL,
`motivotransf` VARCHAR(1000) NULL DEFAULT NULL,
`codcurso` INT(11) NULL DEFAULT NULL,
`codunidade` INT(11) NULL DEFAULT NULL,
`situacaoaluno` varchar(100) NULL DEFAULT NULL,
`rematriculamassa` CHAR(1) NULL DEFAULT NULL,
`codconfturma` INT(11) NULL DEFAULT NULL,
`cpfrespfinanceiro2` VARCHAR(14) NULL DEFAULT NULL,
`parentescorespfinanceiro2` varchar(100) NULL DEFAULT NULL,
`financeiroporcurso` CHAR(1) NULL DEFAULT 'N',
`integral` CHAR(1) NULL DEFAULT NULL,
`tecnicococomitante` CHAR(1) NULL DEFAULT 'N',
`matonline` CHAR(1) NULL DEFAULT 'N',
`codconffatura` INT(11) NULL DEFAULT '1',
`intercambista` CHAR(1) NULL DEFAULT NULL COMMENT 'S - Sim / N- Nao',
`codconfturma_aux` varchar(100) NULL DEFAULT NULL,
codaluno_tella int(11),
PRIMARY KEY (`codmatricula`) USING BTREE,
INDEX `idx_codconfturma_aux` (`CODCONFTURMA_AUX`),
INDEX `idx_codaluno` (`CODALUNO`),
INDEX `idx_codmatricula` (`CODMATRICULA`))
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;", conn);
create.ExecuteNonQuery();
//cria a funcao gerasenha
MySqlCommand function = new MySqlCommand($@"
DROP FUNCTION IF EXISTS gerasenha;
CREATE DEFINER=`root`@`%` FUNCTION `gerasenha`(`tamanho` int)
RETURNS varchar(10) CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
return (SELECT lower(CONVERT(SUBSTRING(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
MD5(RAND())
,'1','')
,'0','')
,'a','')
,'e','')
,'i','')
,'o','')
,'u','')
FROM 1 FOR tamanho) USING latin1)));
END", conn);
function.ExecuteNonQuery();
FbCommand MySelect = new FbCommand(@"select
distinct(a.codigo) as codmatricula,
replace(a.prontuario,'-','') as codaluno,
right(sc.anoletivo,2)||lpad((sc.unidade || sc.curso || sc.serie),6,0)||lpad(t.codturma,4,0) as codconfturma_aux,
(a.unidade || a.curso || a.serie) as codseriecurso,
t.codturma as codturma,
EXTRACT(YEAR FROM a.datamatric) || '-' || EXTRACT(MONTH FROM a.datamatric) || '-' || EXTRACT(DAY FROM a.datamatric) as dtmatricula,
a.anoletivo as anoletivo,
iif(replace(replace(iif(a.cpfresp='',null, a.cpfresp),'-',''),'.','') = ' ','00000000000',replace(replace(iif(a.cpfresp='',null, a.cpfresp),'-',''),'.','')) as cpfrespfinanceiro,
iif(replace(replace(iif(a.cpf_rp ='',null, cpf_rp),'-',''),'.','') = ' ','00000000000', replace(replace(iif(a.cpf_rp ='',null, cpf_rp),'-',''),'.','')) as cpfrespacademico,
CASE a.situacao when 'C' then 'I' when 'A' then 'C' when'T' then 'T' when 'F' then 'F' END as situacao,
EXTRACT(YEAR FROM current_timestamp) || '-' || EXTRACT(MONTH FROM current_timestamp) || '-' || EXTRACT(DAY FROM current_timestamp) as dtcadastro,
'1' as cadastradopor,
(a.unidade || a.curso) as codcurso,
a.unidade as codunidade,
iif(a.situacao = 'C' or (a.situacao = 'T'),EXTRACT(YEAR FROM a.datasit) || '-' || EXTRACT(MONTH FROM a.datasit) || '-' || EXTRACT(DAY FROM a.datasit),null) as dttransferencia,
-- a.anoletivo,
-- t.codturma,
-- a.turno,
'S' as rematricula
from sigaluno a
left join sigclass sc on (sc.anoletivo = a.anoletivo
and sc.unidade = a.unidade
and sc.curso = a.curso
and sc.serie = a.serie
and sc.classe = iif(a.classe = '', 'A', a.classe)
and sc.turno = a.turno)
left join turma_tella t on t.dscturma = (COALESCE(sc.classe,'X') || COALESCE(sc.turno,'Y') || COALESCE(sc.periodo,'Z'))
where t.codturma is not null
and a.prontuario is not null", conn2);
DataTable dtable = new DataTable();
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
try
{
queryBuilder.Append("INSERT INTO mig_matricula_siga (codmatricula,codaluno,codconfturma_aux,codseriecurso,codturma,dtmatricula,anoletivo,cpfrespfinanceiro,cpfrespacademico,situacao,dtcadastro,cadastradopor,codcurso,codunidade,dttransferencia,rematricula) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["codmatricula"]}' , '{dtable.Rows[i]["codaluno"]}' , '{dtable.Rows[i]["codconfturma_aux"]}' , '{dtable.Rows[i]["codseriecurso"]}' , '{dtable.Rows[i]["codturma"]}' , '{dtable.Rows[i]["dtmatricula"]}' , '{dtable.Rows[i]["anoletivo"]}' , '{dtable.Rows[i]["cpfrespfinanceiro"]}' , '{dtable.Rows[i]["cpfrespacademico"]}' , '{dtable.Rows[i]["situacao"]}' , '{dtable.Rows[i]["dtcadastro"]}' ,'{dtable.Rows[i]["cadastradopor"]}' , '{dtable.Rows[i]["codcurso"]}' , '{dtable.Rows[i]["codunidade"]}' , '{dtable.Rows[i]["dttransferencia"]}' , '{dtable.Rows[i]["rematricula"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
insert = new MySqlCommand(queryBuilder.ToString(), conn);
insert.ExecuteNonQuery();
}
catch (Exception err)
{
if (err.Message == "Packets larger than max_allowed_packet are not allowed.")
{
for (int i = 0; i < dtable.Rows.Count; i++)
{
insert.CommandText = $@"INSERT INTO mig_matricula_siga (codmatricula,codaluno,codconfturma_aux,codseriecurso,codturma,dtmatricula,anoletivo,cpfrespfinanceiro,cpfrespacademico,situacao,dtcadastro,cadastradopor,codcurso,codunidade,dttransferencia,rematricula) VALUES
('{dtable.Rows[i]["codmatricula"]}' , '{dtable.Rows[i]["codaluno"]}' , '{dtable.Rows[i]["codconfturma_aux"]}' , '{dtable.Rows[i]["codseriecurso"]}' , '{dtable.Rows[i]["codturma"]}' , '{dtable.Rows[i]["dtmatricula"]}' , '{dtable.Rows[i]["anoletivo"]}' , '{dtable.Rows[i]["cpfrespfinanceiro"]}' , '{dtable.Rows[i]["cpfrespacademico"]}' , '{dtable.Rows[i]["situacao"]}' , '{dtable.Rows[i]["dtcadastro"]}' ,'{dtable.Rows[i]["cadastradopor"]}' , '{dtable.Rows[i]["codcurso"]}' , '{dtable.Rows[i]["codunidade"]}' , '{dtable.Rows[i]["dttransferencia"]}' , '{dtable.Rows[i]["rematricula"]}');";
insert.ExecuteNonQuery();
}
}
else
{
MessageBox.Show(err.Message);
conn2.Close();
conn.Close();
}
}
MySqlCommand updates = new MySqlCommand(@"
-- SETANDO O CODCONFTURMA
UPDATE mig_matricula_siga m
join configturma c on c.codconfturma_aux = m.codconfturma_aux
SET m.codconfturma = c.codconfturma;
-- SETANDO O CODALUNO_TELLA
UPDATE mig_matricula_siga m
JOIN mig_aluno_siga a ON a.codaluno = m.codaluno
SET m.codaluno_tella = a.codaluno_tella;
-- ACERTANDO OS RESPS SEM CPF
UPDATE mig_matricula_siga m
SET m.cpfrespfinanceiro = '00000000000'
WHERE m.cpfrespfinanceiro IS NULL;
UPDATE mig_matricula_siga m
SET m.cpfrespacademico = '00000000000'
WHERE m.cpfrespacademico IS NULL;
-- ACERTANDO OS CPFs INVALIDOS
UPDATE mig_matricula_siga m
set m.cpfrespfinanceiro = '00000000000'
WHERE m.cpfrespfinanceiro NOT IN (SELECT a.cpf FROM responsavel a WHERE a.cpf = m.cpfrespfinanceiro) ;
UPDATE mig_matricula_siga m
SET m.cpfrespacademico = '00000000000'
WHERE m.cpfrespacademico NOT IN (SELECT a.cpf FROM responsavel a WHERE a.cpf = m.cpfrespacademico);", conn);
updates.ExecuteNonQuery();
MySqlCommand update = new MySqlCommand(@$ "
UPDATE mig_matricula_siga set dtmatricula = NULL where dtmatricula = '';
UPDATE mig_matricula_siga set dtinicio = NULL where dtinicio = '';
update mig_matricula_siga set dtfim = null where dtfim = '';
update mig_matricula_siga set dtcadastro = null where dtcadastro = '';
update mig_matricula_siga set dtalteracao = null where dtalteracao = '';
update mig_matricula_siga set dtTransferencia = null where dtTransferencia = '';", conn);
update.ExecuteNonQuery();
MySqlCommand insertMatricula = new MySqlCommand(@"SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO matricula (codmatricula, codaluno, codseriecurso, codturma, dtmatricula, anoletivo, dtinicio, dtfim, cpfrespacademico, cpfrespfinanceiro, parentescorespacademico, parentescorespfinanceiro, rematricula, situacao, dtcadastro, cadastradopor, qtdparcela, primeirovenc, valoranuidade, codescolaorigem, dtalteracao, alteradopor, desconto, bolsista, codbolsa, alunorespacademico, alunorespfinanceiro, valordiscipadicional, dtTransferencia, motivotransf, codcurso, codunidade, situacaoaluno, rematriculamassa, codconfturma, cpfrespfinanceiro2, parentescorespfinanceiro2, financeiroporcurso, integral, tecnicococomitante, matonline, codconffatura, intercambista )
(SELECT codmatricula, codaluno_tella, codseriecurso, codturma, dtmatricula, anoletivo, dtinicio, dtfim, cpfrespacademico, cpfrespfinanceiro, parentescorespacademico, parentescorespfinanceiro, rematricula, situacao, dtcadastro, cadastradopor, qtdparcela, primeirovenc, valoranuidade, codescolaorigem, dtalteracao, alteradopor, desconto, bolsista, codbolsa, alunorespacademico, alunorespfinanceiro, valordiscipadicional, dtTransferencia, motivotransf, codcurso, codunidade, situacaoaluno, rematriculamassa, codconfturma, cpfrespfinanceiro2, parentescorespfinanceiro2, financeiroporcurso, integral, tecnicococomitante, matonline, codconffatura, intercambista
FROM mig_matricula_siga m
WHERE m.codconfturma > 0 and m.codaluno_tella > 0);", conn);
insertMatricula.ExecuteNonQuery();
MySqlCommand query2 = new MySqlCommand(@"
-- LOGINS
-- Rodar os scripts abaixo para gerar as senhas dos funcionarios, alunos e responsáveis.
UPDATE aluno a SET senha = gerasenha(6);
UPDATE responsavel a SET senha = gerasenha(6);
UPDATE funcionario a SET senha = gerasenha(6);
-- insere um registro para nao bugar os inserts debaixo
INSERT INTO login (id) VALUES (1);
-- INSERE OS ALUNOS PENDENTES NA TABELA LOGIN.
INSERT INTO login (codaluno, login2, perfil, senha)
SELECT a.codaluno, a.codaluno, 'A', IFNULL(a.senha, gerasenha(6)) AS senha
FROM aluno a
WHERE a.codaluno NOT IN (
SELECT L.codaluno AS contador
FROM login L
WHERE L.codaluno = a.codaluno);
-- insere responsaveis
INSERT INTO login (login2, perfil, senha, codsoc)
SELECT r.cpf, 'R', IFNULL(r.senha, gerasenha(6)) AS senha, r.codsoc
FROM responsavel r
WHERE r.codsoc NOT IN (
SELECT L.codsoc AS contador
FROM login L
WHERE L.codsoc = r.codsoc);
-- insere funcionarios
INSERT INTO login (login2, perfil, senha, funcodigo)
SELECT r.LOGIN, 'P', IFNULL(r.senha, gerasenha(6)) AS senha, r.funcodigo
FROM funcionario r
WHERE r.funcodigo NOT IN (
SELECT L.funcodigo AS contador
FROM login L
WHERE L.funcodigo = r.funcodigo);
UPDATE login, empresa SET login = CAST(CONCAT(login.perfil, SUBSTRING(empresa.codempresatella,3,4),empresa.codunidadetella, IFNULL(IFNULL(login.id,999),999)) AS CHAR);
UPDATE login l JOIN aluno a ON a.codaluno = l.codaluno SET l.senha = a.senha;
UPDATE login l JOIN funcionario f ON f.FUNCODIGO = l.funcodigo SET l.senha = f.SENHA;
UPDATE login l JOIN responsavel r ON r.codsoc = l.codsoc SET l.senha = r.senha;
UPDATE matricula m SET m.dttransferencia = NULL WHERE m.dttransferencia = 0;", conn);
query2.CommandTimeout = 5000;
query2.ExecuteNonQuery();
MessageBox.Show("Talvez existam matrículas na tabela mig_matricula_siga que estejam com o codconfturma = 0," +
"e que por isso, não estão na tabela matrícula, verifique!");
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from matricula;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
DataTable dtable = new DataTable();
FbCommand MySelect = new FbCommand(@"select (s.unidade || s.curso || s.serie) as codseriecurso,
s.unidade as codunidade,
(s.unidade || s.curso) as codcurso,
s.serie as codserie,
max(IIF(c.tipo = 'L', (s.serie||'º UNICA') , IIF(c.descricao like '%ANO%', (s.serie||'º ANO'),(s.serie||'º SERIE')))) as dscserie,
max('S') as ativo,
s.serie as ORDEM,
max('N') as CONCLUINTE,
'MIGRACAO2020' as cadastradopor
FROM sigserie s
join sigcurso c on s.curso = c.codigo
group by s.unidade, s.curso, s.serie;", conn2);
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("SET FOREIGN_KEY_CHECKS = 0; " +
"DELETE FROM seriecurso;" +
"DELETE FROM cursosunidade;" +
"INSERT INTO seriecurso (codseriecurso, codunidade, codcurso, codserie, dscserie, ativo, ordem, concluinte, cadastradopor) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["codseriecurso"]}' , '{dtable.Rows[i]["codunidade"]}' , '{dtable.Rows[i]["codcurso"]}' , '{dtable.Rows[i]["codserie"]}' ,'{dtable.Rows[i]["dscserie"]}' , '{dtable.Rows[i]["ativo"]}' , '{dtable.Rows[i]["ordem"]}' , '{dtable.Rows[i]["concluinte"]}' , '{dtable.Rows[i]["cadastradopor"]}'), ");
}
queryBuilder.Remove(queryBuilder.Length - 2, 2);
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
// CURSOS UNIDADE //
DataTable dtable2 = new DataTable();
MySqlCommand MySelect3 = new MySqlCommand(@"SELECT CODUNIDADE, CODCURSO, 'N' FROM seriecurso GROUP BY CODUNIDADE, CODCURSO;", conn);
MySqlDataAdapter adapter2 = new MySqlDataAdapter(MySelect3);
adapter2.Fill(dtable2);
StringBuilder queryBuilder2 = new StringBuilder();
queryBuilder2.Append("SET FOREIGN_KEY_CHECKS = 0; " +
"INSERT INTO cursosunidade (codunidade, codcurso, portalsimplificado) VALUES ");
for (int i = 0; i < dtable2.Rows.Count; i++)
{
queryBuilder2.Append($@"('{dtable2.Rows[i]["codunidade"]}' , '{dtable2.Rows[i]["codcurso"]}' , '{dtable2.Rows[i]["N"]}'), ");
}
queryBuilder2.Remove(queryBuilder2.Length - 2, 2);
MySqlCommand query2 = new MySqlCommand(queryBuilder2.ToString(), conn);
query2.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from seriecurso;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
public void ExecutarProcedimento(params object[] args)
{
RecordLog r = new RecordLog();
MySqlCommand log = new MySqlCommand();
string MySQL = $@"server = {args[1]}; user id = {args[2]}; database = {args[0]}; password = {args[7]};";
MySqlConnection conn = new MySqlConnection(MySQL);
conn.Open();
log.Connection = conn;
string FbConn = $@"DataSource = {args[4]}; Database = {args[3]}; username = {args[5]}; password = {args[6]}; CHARSET = NONE;";
FbConnection conn2 = new FbConnection(FbConn);
conn2.Open();
try
{
DataTable dtable = new DataTable();
FbCommand MySelect = new FbCommand(@"select
d.codigo as coddisciplina,
d.resumo as dscabreviada,
d.descricao as dscdisciplina,
'S' as ativo,
1 as pesoreprovacao
from sigdisci d;", conn2);
//Crie um data adapter para trabalhar com datatables.
//Datatables são mais fáceis de manipular que utilizar o método Read()
FbDataAdapter adapter = new FbDataAdapter(MySelect);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_disciplina_siga;
DELETE FROM disciplina;
CREATE TABLE mig_disciplina_siga (`coddisciplina` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`codarea` INT(10) UNSIGNED NULL DEFAULT NULL,
`dscdisciplina` VARCHAR(120) NOT NULL COLLATE 'latin1_swedish_ci',
`dscabreviada` VARCHAR(5) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`ativo` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`optativa` CHAR(1) NULL DEFAULT 'N' COLLATE 'latin1_swedish_ci',
`nucleo` varchar(100) NULL DEFAULT NULL COMMENT 'determina a base da disciplina; Comun ou diversificada.' COLLATE 'latin1_swedish_ci',
`coddisciplinacenso` INT(2) NULL DEFAULT NULL COMMENT 'disciplina a qual é referente no censo.',
`pesoreprovacao` DECIMAL(5,2) NULL DEFAULT '1.00' COMMENT 'determina o peso na disciplina pra calcular a quantidade de disciplinas reprovadas.',
coddisciplina_tella int(11),
PRIMARY KEY (`coddisciplina`) USING BTREE,
INDEX `DISCIPLINA_FKIndex1` (`codarea`) USING BTREE);" +
"INSERT INTO mig_disciplina_siga (coddisciplina,dscabreviada,dscdisciplina,ativo,pesoreprovacao) VALUES ");
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["coddisciplina"]}' , '{dtable.Rows[i]["dscabreviada"]}' , '{dtable.Rows[i]["dscdisciplina"]}' , '{dtable.Rows[i]["ativo"]}' , '{dtable.Rows[i]["pesoreprovacao"]}'), ");
}
//Remove a última vírgula da consulta, para evitar erros de sintaxe.
queryBuilder.Remove(queryBuilder.Length - 2, 2);
//O segredo da perfomace está aqui: Você somente executa a operação após construir toda a consulta.
//Antes você estava executando uma chamada no banco de dados a cada iteração do while. E isto é um pecado, em relação a perfomace =D
//var s = queryBuilder.ToString();
MySqlCommand query = new MySqlCommand(queryBuilder.ToString(), conn);
query.ExecuteNonQuery();
MySqlCommand insert = new MySqlCommand(@"SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO disciplina (coddisciplina,dscabreviada,dscdisciplina,ativo,pesoreprovacao)
SELECT max(coddisciplina) as coddisciplina,dscabreviada,dscdisciplina,ativo,pesoreprovacao
FROM mig_disciplina_siga GROUP BY dscdisciplina;
UPDATE mig_disciplina_siga ds JOIN disciplina d ON d.dscdisciplina = ds.dscdisciplina SET ds.coddisciplina_tella = d.coddisciplina;", conn);
insert.ExecuteNonQuery();
MessageBox.Show("Importação concluída com sucesso!");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
log.CommandText = "select count(1) from disciplina;";
var qtd = Convert.ToInt32(log.ExecuteScalar());
r.Record(args[8].ToString(), qtd);
conn2.Close();
conn.Close();
}
}
