public StringBuilder GeraInsertSqlGDef(GDefCompl gDefCompl)
{
StringBuilder builder = new StringBuilder();
var sentenca = $@"
/*************************************************************
GDEFCOMPL - {gDefCompl.TabelaDados} - {gDefCompl.NomeColuna}
*************************************************************/
DECLARE @COLIGADA INT
IF NOT EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE NAME = N'{gDefCompl.NomeColuna}' and Object_ID = Object_ID(N'{gDefCompl.TabelaDados}'))
BEGIN
ALTER TABLE {gDefCompl.TabelaDados} ADD {gDefCompl.NomeColuna} VARCHAR({gDefCompl.TamanhoColuna}) NULL
DECLARE CURSOR_COLIGADA CURSOR FOR SELECT CODCOLIGADA FROM GCOLIGADA WHERE CODCOLIGADA <> 0
OPEN CURSOR_COLIGADA
FETCH NEXT FROM CURSOR_COLIGADA INTO @COLIGADA
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO dbo.GDEFCOMPL (CODCOLIGADA, APLICACAO, TABDADOS, NOMECOLUNA, DESCRICAO, CODTABDINAM, CODFORMULA, VALORDEFAULT, CODCOLTABDINAM, APLICTABDINAM, ORDEM, QUEBRALINHA, PESQTABDINAMPORCOD, CODCOLFORMULA, CODAPLICFORMULA, TIPOTEXTO, RECCREATEDBY, RECCREATEDON, RECMODIFIEDBY, RECMODIFIEDON)
VALUES (@COLIGADA, '{gDefCompl.Aplicacao}', '{gDefCompl.TabelaDados}', '{gDefCompl.NomeColuna}', '{gDefCompl.Descricao}', null, NULL, NULL, 0, 'S', {gDefCompl.Ordem}, NULL, 'T', NULL, NULL, '0', 'mestre', GETDATE(), NULL, NULL)
FETCH NEXT FROM CURSOR_COLIGADA INTO @COLIGADA
END
CLOSE CURSOR_COLIGADA
DEALLOCATE CURSOR_COLIGADA
END
GO {Environment.NewLine}";
return(builder.Append(sentenca));
}
public StringBuilder GeraInsertOracleGDef(GDefCompl gDefCompl)
{
return(new StringBuilder()
.AppendLine($@"
DECLARE EXIST INTEGER;
BEGIN
/*************************************************************
GDEFCOMPL - {gDefCompl.TabelaDados} - {gDefCompl.NomeColuna}
*************************************************************/
SELECT COUNT(*) INTO EXIST FROM user_tab_cols where column_name = '{gDefCompl.NomeColuna}' and table_name = '{gDefCompl.TabelaDados}';
IF EXIST = 0 THEN
EXECUTE IMMEDIATE
'ALTER TABLE {gDefCompl.TabelaDados} ADD {gDefCompl.NomeColuna} VARCHAR(60) NULL';
DECLARE V_COLIGADA2 INTEGER;
CURSOR COLIGADA_CUR2 IS SELECT CODCOLIGADA FROM GCOLIGADA WHERE CODCOLIGADA <> 0;
BEGIN
OPEN COLIGADA_CUR2;
LOOP
FETCH COLIGADA_CUR2 INTO V_COLIGADA2;
EXIT WHEN COLIGADA_CUR2 % NOTFOUND;
INSERT INTO GDEFCOMPL(CODCOLIGADA, APLICACAO, TABDADOS, NOMECOLUNA, DESCRICAO, CODTABDINAM, CODFORMULA, VALORDEFAULT, CODCOLTABDINAM, APLICTABDINAM, ORDEM, QUEBRALINHA, PESQTABDINAMPORCOD, CODCOLFORMULA, CODAPLICFORMULA, TIPOTEXTO, RECCREATEDBY, RECCREATEDON, RECMODIFIEDBY, RECMODIFIEDON)
VALUES(V_COLIGADA2,'{gDefCompl.Aplicacao}', '{gDefCompl.TabelaDados}', '{gDefCompl.NomeColuna}', '{gDefCompl.Descricao}', null, NULL, NULL, 0, 'S', {gDefCompl.Ordem}, NULL, 'T', NULL, NULL, '0', 'mestre', SYSDATE, NULL, NULL);
END LOOP;
CLOSE COLIGADA_CUR2;
END;
END IF {Environment.NewLine}"));
}
private GDefCompl GetDadosGDefCompl()
{
GDefCompl gDefCompl = new GDefCompl();
gDefCompl.TamanhoColuna = Convert.ToInt32(txtGDefTamanhoColuna.Text);
gDefCompl.Aplicacao = txtGdefAplicacao.Text.ToUpper();
gDefCompl.TabelaDados = txtGDefTabelaDados.Text.ToUpper();
gDefCompl.NomeColuna = txtGDefNomeColuna.Text.ToUpper();
gDefCompl.Descricao = txtGDefDescricao.Text.ToUpper();
gDefCompl.CodTabelaDinamica = txtGDefCodigoTabelaDinamica.Text;
gDefCompl.Codformula = txtGDefCodigoFormula.Text;
gDefCompl.ValorDefault = txtGdefValorDefault.Text;
gDefCompl.CodColigadaTabelaDinamica = Convert.ToInt32(txtGDefCodigoColigadaTabelaDinamica.Text);
gDefCompl.AplicacaoTabelaDinamica = txtGDefAplicacaoTabelaDinamica.Text;
gDefCompl.Ordem = Convert.ToInt32(txtGdefOrdem.Text);
gDefCompl.QuebraLinha = (chkBGdefQuebraLinha.Checked) ? "T" : "F";
gDefCompl.PesquisaTabelaDinamicaPorCodigo = (chkBoxGDefPesqTabDinamCod.Checked) ? "T" : "F";
gDefCompl.CodColigadaFormula = Convert.ToInt32(txtGdefCodigoColigadaFormula.Text);
gDefCompl.CodigoAplicacaoFormula = txtGDefCodApliFormula.Text;
gDefCompl.TipoTexto = txtGDefTamanhoColuna.Text.ToUpper();
return(gDefCompl);
}
