/// <summary>
/// 保存为excel表格，并对表格进行同步
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool DataGridtoExcelModify(System.Data.DataTable dt)
{
#region
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl 表格文件 (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = false;
saveFileDialog.Title = "导出Excel文件";
//设置默认文件名称
saveFileDialog.FileName = "报表-" + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xls";
if (saveFileDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return(false);
}
if (saveFileDialog.FileName == "")
{
return(false);
}
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
object str = string.Empty;
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i > 0)
{
str += "\t";
}
str += dt.Columns[i].ColumnName;
}
sw.WriteLine(str);
for (int j = 0; j < dt.Rows.Count; j++)
{
object tempStr = string.Empty;
for (int k = 0; k < dt.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dt.Rows[j][k].ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
return(true);
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
RecordLog.GetInstance().WriteLog(Level.Error, "DataGridtoExcel:" + ex.Message);
return(true);
}
finally
{
sw.Close();
myStream.Close();
}
#endregion
}
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();
int anoMenor = 0;
int anoMaior = 0;
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();
MySqlCommand insert = new MySqlCommand("", conn);
insert.CommandTimeout = 86400;
try
{
MySqlCommand create = new MySqlCommand($@"SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS mig_acumulado_siga;
DELETE FROM acumulado;
DELETE FROM acumuladoagrupado;
DELETE FROM turmadiario;
CREATE TABLE `mig_acumulado_siga` (
`CODACUMULADO` INT(11) NOT NULL AUTO_INCREMENT,
`MATRICULA` varchar(100) NOT NULL,
`CODMATERIA` varchar(100) NOT NULL,
`ANO` varchar(100) NOT NULL,
`CODSERIE` varchar(100) NOT NULL,
`CODTURMA` varchar(100) NOT NULL,
`MEDIA1` varchar(100) NULL DEFAULT NULL,
`FALTAS1` varchar(100) NULL DEFAULT NULL,
`MEDIA2` varchar(100) NULL DEFAULT NULL,
`FALTAS2` varchar(100) NULL DEFAULT NULL,
`MEDIA3` varchar(100) NULL DEFAULT NULL,
`FALTAS3` varchar(100) NULL DEFAULT NULL,
`MEDIA4` varchar(100) NULL DEFAULT NULL,
`FALTAS4` varchar(100) NULL DEFAULT NULL,
`RECSEMESTRE1` varchar(100) NULL DEFAULT NULL,
`RECSEMESTRE2` varchar(100) NULL DEFAULT NULL,
`RECFINAL` varchar(100) NULL DEFAULT NULL,
`RECFINAL2` varchar(100) NULL DEFAULT NULL,
`RECFINAL3` varchar(100) NULL DEFAULT NULL,
`alterado` CHAR(1) NULL DEFAULT NULL,
`MEDSEM1` varchar(100) NULL DEFAULT NULL,
`MEDSEM2` varchar(100) NULL DEFAULT NULL,
`RESULTFINAL` varchar(100) NULL DEFAULT NULL,
`TOTFALTAS` varchar(100) NULL DEFAULT NULL,
`SITUACAO` CHAR(1) NULL DEFAULT NULL,
`codprofturma` varchar(100) NULL DEFAULT NULL,
`rec1` varchar(100) NULL DEFAULT NULL,
`rec2` varchar(100) NULL DEFAULT NULL,
`rec3` varchar(100) NULL DEFAULT NULL,
`rec4` varchar(100) NULL DEFAULT NULL,
`soma1` varchar(100) NULL DEFAULT NULL,
`soma2` varchar(100) NULL DEFAULT NULL,
`soma3` varchar(100) NULL DEFAULT NULL,
`soma4` varchar(100) NULL DEFAULT NULL,
`somasemestre1` varchar(100) NULL DEFAULT NULL,
`somasemestre2` varchar(100) NULL DEFAULT NULL,
`CODPROFESSOR` varchar(100) NULL DEFAULT NULL,
`usuario` varchar(100) NULL DEFAULT NULL,
`codcalculoacumulado` varchar(100) NULL DEFAULT NULL,
`dtvalidacaocalculo` varchar(100) NULL DEFAULT NULL,
`somaetapas` varchar(100) NULL DEFAULT NULL,
`mig_conceito1` VARCHAR(5) NULL DEFAULT NULL,
`mig_conceito2` VARCHAR(5) NULL DEFAULT NULL,
`mig_conceito3` VARCHAR(5) NULL DEFAULT NULL,
`mig_conceito4` VARCHAR(5) NULL DEFAULT NULL,
`mig_conceitofinal` VARCHAR(5) NULL DEFAULT NULL,
`mig_pontosaprovconselho` varchar(100) NULL DEFAULT NULL,
PRIMARY KEY (`CODACUMULADO`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;", conn);
create.ExecuteNonQuery();
ResetaValorAcumulado.Resetar(out anoMenor, out anoMaior, FbConn);
while (anoMenor <= anoMaior)
{
dtable.Clear();
FbCommand select = new FbCommand($@"select
-- d.descricao,
m.codigo as MATRICULA,
a.disciplina as CODMATERIA,
a.anoletivo as ANO,
(m.unidade || m.curso || m.serie) as CODSERIE,
tt.codturma as CODTURMA,
iif(a.resultbim1 in ('A','B','C','D','E','DISP'),NULL, a.resultbim1) as MEDIA1,
a.faltabim1 as FALTAS1,
iif(a.resultbim2 in ('A','B','C','D','E','DISP'),NULL, a.resultbim2) as MEDIA2,
a.faltabim2 as FALTAS2,
iif(a.resultbim3 in ('A','B','C','D','E','DISP'),NULL, a.resultbim3) as MEDIA3,
a.faltabim3 as FALTAS3,
iif(a.resultbim4 in ('A','B','C','D','E','DISP'),NULL, a.resultbim4) as MEDIA4,
a.faltabim4 as FALTAS4,
iif(a.notarecup in ('A','B','C','D','E','DISP'),NULL, a.notarecup) as RECFINAL,
iif(a.mediafinal in ('A','B','C','D','E','DISP'),NULL, a.mediafinal) as RESULTFINAL,
(coalesce(a.faltabim1,0) + coalesce(a.faltabim2,0) + coalesce(a.faltabim3,0) + coalesce(a.faltabim4,0)) as TOTFALTAS,
iif(a.recupbim1 in ('A','B','C','D','E','DISP'),NULL, a.recupbim1) as rec1,
iif(a.recupbim2 in ('A','B','C','D','E','DISP'),NULL, a.recupbim2) as rec2,
iif(a.recupbim3 in ('A','B','C','D','E','DISP'),NULL, a.recupbim3) as rec3,
iif(a.recupbim4 in ('A','B','C','D','E','DISP'),NULL, a.recupbim4) as rec4,
iif(a.mediabim1 in ('A','B','C','D','E','DISP'),NULL, a.mediabim1) as soma1,
iif(a.mediabim2 in ('A','B','C','D','E','DISP'),NULL, a.mediabim2) as soma2,
iif(a.mediabim3 in ('A','B','C','D','E','DISP'),NULL, a.mediabim3) as soma3,
iif(a.mediabim4 in ('A','B','C','D','E','DISP'),NULL, a.mediabim4) as soma4,
iif(a.mediabim in ('A','B','C','D','E','DISP'),NULL, a.mediabim) as somaetapas,
iif(a.resultbim1 in ('A','B','C','D','E'),a.resultbim1,NULL ) as mig_conceito1,
iif(a.resultbim2 in ('A','B','C','D','E'),a.resultbim2,NULL ) as mig_conceito2,
iif(a.resultbim3 in ('A','B','C','D','E'),a.resultbim3,NULL ) as mig_conceito3,
iif(a.resultbim4 in ('A','B','C','D','E'),a.resultbim4,NULL ) as mig_conceito4,
iif(a.mediafinal in ('A','B','C','D','E'),a.mediafinal,NULL ) as mig_conceitofinal,
iif(a.notaconse in ('A','B','C','D','E','DISP'),NULL, a.notaconse) as mig_pontosaprovconselho -- ,
-- a.*
from signotfa a
join sigaluno m on (a.unidade = m.unidade and a.anoletivo = m.anoletivo and a.periodo = m.periodo and a.curso = m.curso and a.serie = m.serie and a.classe = m.classe and a.turno = m.turno and a.chamada = m.chamada )
join sigclass t on (a.unidade = t.unidade and a.anoletivo = t.anoletivo and a.periodo = t.periodo and a.curso = t.curso and a.serie = t.serie and a.classe = t.classe and a.turno = t.turno)
join turma_tella tt on tt.dscturma = (COALESCE(t.classe,'X') || COALESCE(t.turno,'Y') || COALESCE(t.periodo,'Z'))
join sigdisci d on (d.codigo = a.disciplina)
where a.anoletivo = {anoMenor} and a.resultbim1 is null", conn2);
FbDataAdapter adapter = new FbDataAdapter(select);
adapter.Fill(dtable);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append($@"INSERT INTO mig_acumulado_siga (MATRICULA, CODMATERIA, ANO, CODSERIE, CODTURMA, MEDIA1, FALTAS1, MEDIA2, FALTAS2, MEDIA3, FALTAS3, MEDIA4, FALTAS4, RECFINAL, RESULTFINAL, TOTFALTAS, rec1, rec2, rec3, rec4, soma1, soma2, soma3, soma4, somaetapas, mig_conceito1, mig_conceito2, mig_conceito3, mig_conceito4, mig_conceitofinal, mig_pontosaprovconselho ) VALUES ");
try
{
for (int i = 0; i < dtable.Rows.Count; i++)
{
queryBuilder.Append($@"('{dtable.Rows[i]["MATRICULA"]}' , '{dtable.Rows[i]["CODMATERIA"]}' , '{dtable.Rows[i]["ANO"]}' , '{dtable.Rows[i]["CODSERIE"]}' , '{dtable.Rows[i]["CODTURMA"]}' , '{dtable.Rows[i]["MEDIA1"]}' , '{dtable.Rows[i]["FALTAS1"]}' , '{dtable.Rows[i]["MEDIA2"]}' , '{dtable.Rows[i]["FALTAS2"]}' ,'{dtable.Rows[i]["MEDIA3"]}' , '{dtable.Rows[i]["FALTAS3"]}' , '{dtable.Rows[i]["MEDIA4"]}' , '{dtable.Rows[i]["FALTAS4"]}' , '{dtable.Rows[i]["RECFINAL"]}' , '{dtable.Rows[i]["RESULTFINAL"]}' , '{dtable.Rows[i]["TOTFALTAS"]}' , '{dtable.Rows[i]["rec1"]}' , '{dtable.Rows[i]["rec2"]}' , '{dtable.Rows[i]["rec3"]}' , '{dtable.Rows[i]["rec4"]}' , '{dtable.Rows[i]["soma1"]}' , '{dtable.Rows[i]["soma2"]}' , '{dtable.Rows[i]["soma3"]}' , '{dtable.Rows[i]["soma4"]}' , '{dtable.Rows[i]["somaetapas"]}' , '{dtable.Rows[i]["mig_conceito1"]}' , '{dtable.Rows[i]["mig_conceito2"]}' , '{dtable.Rows[i]["mig_conceito3"]}' , '{dtable.Rows[i]["mig_conceito4"]}' , '{dtable.Rows[i]["mig_conceitofinal"]}' , '{dtable.Rows[i]["mig_pontosaprovconselho"]}'), ");
}
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_acumulado_siga (MATRICULA, CODMATERIA, ANO, CODSERIE, CODTURMA, MEDIA1, FALTAS1, MEDIA2, FALTAS2, MEDIA3, FALTAS3, MEDIA4, FALTAS4, RECFINAL, RESULTFINAL, TOTFALTAS, rec1, rec2, rec3, rec4, soma1, soma2, soma3, soma4, somaetapas, mig_conceito1, mig_conceito2, mig_conceito3, mig_conceito4, mig_conceitofinal, mig_pontosaprovconselho ) VALUES
('{dtable.Rows[i]["MATRICULA"]}' , '{dtable.Rows[i]["CODMATERIA"]}' , '{dtable.Rows[i]["ANO"]}' , '{dtable.Rows[i]["CODSERIE"]}' , '{dtable.Rows[i]["CODTURMA"]}' , '{dtable.Rows[i]["MEDIA1"]}' , '{dtable.Rows[i]["FALTAS1"]}' , '{dtable.Rows[i]["MEDIA2"]}' , '{dtable.Rows[i]["FALTAS2"]}' ,'{dtable.Rows[i]["MEDIA3"]}' , '{dtable.Rows[i]["FALTAS3"]}' , '{dtable.Rows[i]["MEDIA4"]}' , '{dtable.Rows[i]["FALTAS4"]}' , '{dtable.Rows[i]["RECFINAL"]}' , '{dtable.Rows[i]["RESULTFINAL"]}' , '{dtable.Rows[i]["TOTFALTAS"]}' , '{dtable.Rows[i]["rec1"]}' , '{dtable.Rows[i]["rec2"]}' , '{dtable.Rows[i]["rec3"]}' , '{dtable.Rows[i]["rec4"]}' , '{dtable.Rows[i]["soma1"]}' , '{dtable.Rows[i]["soma2"]}' , '{dtable.Rows[i]["soma3"]}' , '{dtable.Rows[i]["soma4"]}' , '{dtable.Rows[i]["somaetapas"]}' , '{dtable.Rows[i]["mig_conceito1"]}' , '{dtable.Rows[i]["mig_conceito2"]}' , '{dtable.Rows[i]["mig_conceito3"]}' , '{dtable.Rows[i]["mig_conceito4"]}' , '{dtable.Rows[i]["mig_conceitofinal"]}' , '{dtable.Rows[i]["mig_pontosaprovconselho"]}');";
insert.ExecuteNonQuery();
}
}
else
{
MessageBox.Show(err.Message);
conn2.Close();
conn.Close();
}
}
anoMenor++;
}
MySqlCommand update = new MySqlCommand(@$ "
UPDATE mig_acumulado_siga set matricula = NULL where matricula = '';
update mig_acumulado_siga set codmateria = null where codmateria = '';
update mig_acumulado_siga set ano = null where ano = '';
update mig_acumulado_siga set codserie = null where codserie = '';
update mig_acumulado_siga set codturma = null where codturma = '';
UPDATE mig_acumulado_siga set media1 = NULL where media1 = '';
UPDATE mig_acumulado_siga set faltas1 = NULL where faltas1 = '';
update mig_acumulado_siga set media2 = null where media2 = '';
UPDATE mig_acumulado_siga set faltas2 = NULL where faltas2 = '';
update mig_acumulado_siga set media3 = null where media3 = '';
update mig_acumulado_siga set faltas3 = null where faltas3 = '';
update mig_acumulado_siga set media4 = null where media4 = '';
UPDATE mig_acumulado_siga set faltas4 = NULL where faltas4 = '';
UPDATE mig_acumulado_siga set recfinal = NULL where recfinal = '';
update mig_acumulado_siga set resultfinal = null where resultfinal = '';
update mig_acumulado_siga set totfaltas = null where totfaltas = '';
update mig_acumulado_siga set rec1 = null where rec1 = '';
update mig_acumulado_siga set rec2 = null where rec2 = '';
UPDATE mig_acumulado_siga set rec3 = NULL where rec3 = '';
UPDATE mig_acumulado_siga set rec4 = NULL where rec4 = '';
update mig_acumulado_siga set soma1 = null where soma1 = '';
update mig_acumulado_siga set soma2 = null where soma2 = '';
update mig_acumulado_siga set soma3 = null where soma3 = '';
update mig_acumulado_siga set soma4 = null where soma4 = '';
UPDATE mig_acumulado_siga set somaetapas = NULL where somaetapas = '';
UPDATE mig_acumulado_siga set mig_conceito1 = NULL where mig_conceito1 = '';
update mig_acumulado_siga set mig_conceito2 = null where mig_conceito2 = '';
update mig_acumulado_siga set mig_conceito3 = null where mig_conceito3 = '';
update mig_acumulado_siga set mig_conceito4 = null where mig_conceito4 = '';
update mig_acumulado_siga set mig_conceitofinal = null where mig_conceitofinal = '';
update mig_acumulado_siga set mig_pontosaprovconselho = null where mig_pontosaprovconselho = '' or mig_pontosaprovconselho REGEXP '^-?[A-Z]+$';", conn);
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 static void Log(object _str)
{
Debug.Log(_str);
RecordLog.Write(_str as String);
}
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();
}
}
