private void ApagarView(CheckStruct oCheck)
{
string cSqlDrop = "";
string cSqlCreate = "";
cSqlDrop = @"
DROP VIEW IF EXISTS vw_dados_usuario cascade ;
";
oCheck.cSqlViewApagar = cSqlDrop;
// Inclusao das Views
/* cSqlCreate = @"
*
* CREATE OR REPLACE VIEW vw_dados_usuario AS
* select usu.*,iif_string(usu_ativo=false,'Desativado','') as ativo,cad_razao_social,cad_celular,cad_email,cad_telefone,stu_nome,dpu_nome,cad_foto
* from cad_usuario usu
* left join cad_cadastro cad on(usu.cad_id = cad.cad_id)
* left join cad_setor_usuario stu on(usu.stu_id = stu.stu_id)
* left join cad_departamento_usuario dpu on(usu.dpu_id = dpu.dpu_id)
*
* ";
*
* oCheck.AddView(cSqlCreate);
*/
}
private void EstruturaCadastro(CheckStruct oCheck)
{
oCheck.cTableName = "cad_cadastro";
oCheck.cComentario = "Cadastr dos Clientes";
oCheck.AddCampo("cad_id", "serial", 0, 0, "Id Primario", false, "");
oCheck.AddCampo("cad_razao_social", "varchar", 100, 0, "Razao Social", false, "");
oCheck.AddCampo("cad_nome_fantasia", "varchar", 100, 0, "Nome Fantasia", false, "");
oCheck.AddCampo("cad_cnpj_cpf", "varchar", 18, 0, "CNPJ/CPF", false, "");
oCheck.AddCampo("cad_email", "varchar", 100, 0, "Email", false, "");
oCheck.AddCampo("cad_telefone", "varchar", 30, 0, "Telefone", false, "");
oCheck.AddCampo("cad_ativo", "boolean", 0, 0, "Ativo", false, "true");
oCheck.AddCampo("lgr_nome", "varchar", 100, 0, "Rua do Logradouro", false, "");
oCheck.AddCampo("end_numero", "varchar", 10, 0, "Numero", false, "");
oCheck.AddCampo("end_complemento", "varchar", 30, 0, "Complemento", false, "");
oCheck.AddCampo("cid_nome", "varchar", 40, 0, "Cidade", false, "");
oCheck.AddCampo("bai_nome", "varchar", 40, 0, "Bairro", false, "");
oCheck.AddCampo("uf_sigla", "varchar", 2, 0, "UF", false, "");
//oCheck.AddCampo("log_registro", "text", 0, 0, "Log Registro", false, "");
oCheck.addindice("ind_cad_01", "upper(cad_cnpj_cpf)", true);
oCheck.Run();
oCheck.cTableName = "cad_contrato";
oCheck.cComentario = "Cadastr dos Contratos";
oCheck.AddCampo("ctr_id", "serial", 0, 0, "Id Primario", false, "");
oCheck.AddCampo("cad_id", "integer", 0, 0, "Id Cadastro", false, "");
oCheck.AddCampo("prd_id", "integer", 0, 0, "Id Produto", false, "");
oCheck.AddCampo("ctr_data_contrato", "date", 0, 0, "Data Contrato", false, "");
oCheck.AddCampo("ctr_data_cancelado", "timestamp", 0, 0, "Data Cancelamento", false, "");
oCheck.AddCampo("ctr_siuacao", "varchar", 30, 0, "Situação: Ativo/Cancelado", false, "'Ativo'");
oCheck.addindice("ind_ctr_01", "cad_id,prd_id", true);
oCheck.Run();
oCheck.cTableName = "fin_titulo";
oCheck.cComentario = "Titulos a Receber";
oCheck.AddCampo("tit_id", "serial", 0, 0, "Id Primario", false, "");
oCheck.AddCampo("cad_id", "integer", 0, 0, "Id Cadastro", false, "");
oCheck.AddCampo("ctr_id", "integer", 0, 0, "Id Produto", false, "");
oCheck.AddCampo("tit_data_vencimento", "date", 0, 0, "Data Vencimento", false, "");
oCheck.AddCampo("tit_valor", "numeric", 12, 2, "Valor", false, "0");
oCheck.AddCampo("tit_historico", "text", 0, 0, "Historico", false, "");
oCheck.Run();
}
private void AtualizaTrigger(CheckStruct oCheck)
{
string cSql = "";
/*cSql = @"CREATE TRIGGER log_cad_organiacao
* AFTER INSERT OR UPDATE OR DELETE ON cad_organizacao
* FOR EACH ROW
* EXECUTE PROCEDURE sis_log_tabelas();";
*
* oCheck.AddTrigger(cSql);
*/
}
private void VerificarBancoDados()
{
CheckStruct oCheck = new CheckStruct();
progressBarControl1.Visible = true;
oCheck.cLabelTela = lblAtualizacao;
oCheck.prgdlg = progressBarControl1;
oCheck.open();
lblAtualizacao.Text = "Verificando extensões do banco de dados..";
lblAtualizacao.Update();
//oCheck.HabilitarExtension("unaccent");
//oCheck.HabilitarExtension("pg_trgm");
//oCheck.HabilitarExtension("tablefunc");
//oCheck.HabilitarExtension("postgis");
//oCheck.HabilitarExtension("plpgsql");
lblAtualizacao.Text = "Apagando as views..";
lblAtualizacao.Update();
oCheck.cSqlTriggerApagar = @"
DROP TRIGGER IF EXISTS log_cad_centro_custo ON cad_centro_custo cascade;
" ;
// Apagar as view antes da criação e atualização dos dados
ApagarView(oCheck);
lblAtualizacao.Text = "Verificando a estrutura das tabelas...";
lblAtualizacao.Update();
EstruturaCadastro(oCheck);
AtualizaFuncoes(oCheck);
AtualizaTrigger(oCheck);
lblAtualizacao.Text = "Executando todas as modificações estruturais...";
lblAtualizacao.Update();
oCheck.executa();
}
private void AtualizaFuncoes(CheckStruct oCheck)
{
string cSql = "";
cSql = @"CREATE OR REPLACE FUNCTION iif_integer(BOOLEAN, integer, Integer) RETURNS integer AS
$body$
DECLARE
rtVal integer;
BEGIN
rtVal := (SELECT CASE $1 WHEN TRUE THEN $2 ELSE $3 END);
RETURN rtVal;
END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION iif_boolean(BOOLEAN, boolean, boolean) RETURNS boolean AS
$body$
DECLARE
rtVal boolean;
BEGIN
rtVal := (SELECT CASE $1 WHEN TRUE THEN $2 ELSE $3 END);
RETURN rtVal;
END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;";
oCheck.AddFunction(cSql);
cSql = @" CREATE OR REPLACE FUNCTION iif_numerico(BOOLEAN, double PRECISION, double PRECISION) RETURNS double PRECISION AS
$body$
DECLARE
rtVal double PRECISION;
BEGIN
rtVal := (SELECT CASE $1 WHEN TRUE THEN $2 ELSE $3 END);
RETURN rtVal;
END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION iif_date(BOOLEAN, date, Date) RETURNS Date AS
$body$
DECLARE
rtVal Date;
BEGIN
rtVal := (SELECT CASE $1 WHEN TRUE THEN $2 ELSE $3 END);
RETURN rtVal;
END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION iif_string(BOOLEAN, text, text) RETURNS text AS
$body$
DECLARE
rtVal text;
BEGIN
rtVal := (SELECT CASE $1 WHEN TRUE THEN $2 ELSE $3 END);
RETURN rtVal;
END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_remove_tracos (p_texto text)
RETURNS text AS
$$
DECLARE
stextonovo TEXT;
BEGIN
stextonovo := replace(p_texto, '-','') ;
stextonovo := replace(stextonovo, '.','') ;
stextonovo := replace(stextonovo, '/','') ;
stextonovo := replace(stextonovo, '\','') ;
stextonovo := replace(stextonovo, ')','') ;
stextonovo := replace(stextonovo, '(','') ;
stextonovo := replace(stextonovo, ' ','') ;
stextonovo := replace(stextonovo, '_','') ;
RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_remove_acento (p_texto text)
RETURNS text AS
$BODY$
Select translate($1,
'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ',
'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'
);
$BODY$
LANGUAGE sql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
cSql = @" CREATE OR REPLACE FUNCTION exc_locate (p_texto text)
RETURNS text AS
$BODY$
Select cast($1 as text) ;
--Select unaccent(cast($1 as text)) ;
$BODY$
LANGUAGE sql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_locate (p_texto numeric)
RETURNS text AS
$BODY$
Select cast($1 as text) ;
--Select unaccent(cast($1 as text)) ;
$BODY$
LANGUAGE sql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_locate (p_texto smallint)
RETURNS text AS
$BODY$
Select cast($1 as text) ;
--Select unaccent(cast($1 as text)) ;
$BODY$
LANGUAGE sql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_locate (p_texto text)
RETURNS text AS
$BODY$
Select cast($1 as text) ;
--Select unaccent(cast($1 as text)) ;
$BODY$
LANGUAGE sql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION year (p_data date)
RETURNS double precision AS
$BODY$
Select extract(Year from p_data) ;
$BODY$
LANGUAGE sql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION month (p_data date)
RETURNS double precision AS
$BODY$
Select extract(Month from p_data) ;
$BODY$
LANGUAGE sql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION day (p_data date)
RETURNS double precision AS
$BODY$
Select extract(day from p_data) ;
$BODY$
LANGUAGE sql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_ultimo_dia(Mes INTEGER, Ano INTEGER)
RETURNS date AS $$
DECLARE
UltimoDiaMes date;
BEGIN
If Mes <> 12 then
Mes := Mes + 1 ;
else
Mes := 0 ;
Ano := Ano + 1 ;
End if ;
SELECT ((Ano||'/'||(Mes + 1)||'/01'):: DATE - 1)
INTO UltimoDiaMes;
RETURN UltimoDiaMes;
END; $$
LANGUAGE 'plpgsql';";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_proximodiautil(dData date)
RETURNS Date AS
$BODY$
BEGIN
IF date_part('dow', dData) = 0 Then
dData := dData + 1 ;
Else
IF date_part('dow', dData) = 6 Then
dData := dData + 2 ;
End if ;
End if ;
RETURN dData;
END;
$BODY$
LANGUAGE plpgsql STABLE";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_vencimentodiautil(pData date, pDias integer)
RETURNS date AS
$BODY$
declare
Dia integer;
SomaDias integer;
NovaData date;
Begin
SomaDias = 0;
NovaData = pData;
while (SomaDias < pDias) loop
NovaData = NovaData + 1;
Dia = extract(dow from NovaData);
while (Dia = 0 or Dia = 6) loop
NovaData = NovaData + 1;
Dia = extract(dow from NovaData);
end loop;
SomaDias = SomaDias + 1;
end loop;
return NovaData;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION quotestr(Argumento text)
RETURNS text AS
$BODY$
DECLARE
BEGIN
RETURN ''''||Argumento||'''' ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE ;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_retornadiavencimento(Mes text,Dia_Tipo Integer,Dia_Numero Integer,Dia_data date)
RETURNS Date AS
$BODY$
DECLARE
dDataNova Date ;
BEGIN
IF dia_tipo = 1 Then -- Fixo
Begin
dDataNova := cast(substring(Mes,4,7)||'-'||substring(Mes,1,2)||'-'||dia_numero::text as date) ;
EXCEPTION when datetime_field_overflow Then
dDataNova := cps_ultimo_dia(substring(mes,1,2)::integer,substring(mes,4,7)::integer) ;
End ;
Elsif dia_Tipo = 2 Then -- dia corrido
dDataNova := cps_proximodiautil(cast(substring(Mes,4,7)||'-'||substring(Mes,1,2)||'-'||dia_numero::text as date)) ;
ElsIf dia_tipo = 3 Then -- numero de dias uteis no mes
dDataNova := cps_vencimentodiautil(cast(substring(Mes,4,7)||'-'||substring(Mes,1,2)||'-01' as date),dia_numero) ;
ElsIF dia_tipo = 4 Then -- data fixa
dDataNova := dia_data ;
End if ;
if ddataNova < Now()::date then
dDataNova := cps_proximodiautil(Now()::date) ;
End if ;
RETURN dDataNova;
END;
$BODY$
LANGUAGE plpgsql STABLE";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION sis_log_tabelas()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
TextoLog text = '' ;
Antes varchar array;
Depois varchar array;
nomecampo varchar;
nomescampos varchar array;
nomescomentario varchar array ;
cUsuario text ='';
i integer;
texto text;
cChave text = '' ;
cIdCampo text = '' ;
cSql text = '';
nIdRegistro text ;
nIdConexao integer ;
nColIni integer = 0 ;
nIdUsuario integer = 0 ;
BEGIN
TextoLog := '' ;
select pg_backend_pid() into nIdConexao ;
select application_name into cUsuario from pg_Stat_activity where pid = nIdConexao;
select POSITION('IdUser:'******'UPDATE' THEN
texto = cast(OLD as text);
texto = substr(texto,2 ,length(texto)-2);
Antes = string_to_array(texto,',');
texto = cast(NEW as text);
texto = substr(texto,2 ,length(texto)-2);
Depois = string_to_array(texto,',');
nomescampos = ARRAY(select attname FROM pg_attribute WHERE attrelid = TG_RELID AND attstattarget = -1 order by attnum);
for i in 1..array_length(nomescampos, 1)
LOOP
if i = 1 then
nIdRegistro := Depois[i] ;
cIdCampo := nomescampos[i] ;
end if ;
if lower(nomescampos[i]) not in('log_registro') then
if Antes[i] <> Depois[i] THEN
textolog = textolog||nomescampos[i]||': '||Antes[i]||' >> '||Depois[i]||E'\r\n';
end if;
End if;
END LOOP;
if textolog <> '' then
cSql := 'UPDATE '||TG_TABLE_NAME||' SET log_registro = concat( '||Quotestr(now()::timestamp(0)::text||' '||cUsuario||'-'||textolog)||E'\r\n,'||Quotestr(coalesce(OLD.log_registro,''))||') where '||cIdCampo||'=<codigo>';
end if ;
ELSEIF TG_OP = 'INSERT' THEN
texto = cast(NEW as text);
texto = substr(texto,2 ,length(texto)-2);
Depois = string_to_array(texto,',');
nomescampos = ARRAY(select attname FROM pg_attribute WHERE attrelid = TG_RELID AND attstattarget = -1 order by attnum);
textolog := '' ;
for i in 1..array_length(nomescampos, 1)
LOOP
if i = 1 then
nIdRegistro := Depois[i] ;
cIdCampo := nomescampos[i] ;
end if ;
textolog = textolog||nomescampos[i]||':'||Depois[i]||E'\r\n';
if i >= 2 then
exit ;
end if ;
END LOOP;
cSql := 'UPDATE '||TG_TABLE_NAME||' SET log_registro = '||Quotestr(now()::timestamp(0)::text||' '||cUsuario||'-Criação do Registro.'||E'\n\n'||textolog)||' where '||cIdCampo||'= <codigo>';
ELSEIF TG_OP = 'DELETE' THEN
texto = cast(OLD as text);
texto = substr(texto,2 ,length(texto)-2);
Antes = string_to_array(texto,',');
nIdRegistro := Antes[1];
cSql := 'insert into sis_arquivo_morto(usu_id,mor_data_exclusao,mor_tabela,mor_script_criacao,mor_id_registro) values('||nIdUsuario::text||',now()::timestamp(0),'||quotestr(TG_TABLE_NAME)||','||quotestr(OLD.log_registro)||',<codigo>)';
END if ;
if cSql <> '' then
cSql := REPLACE(cSql, '<codigo>', nIdRegistro) ;
EXECUTE cSql ;
end if ;
RETURN NULL;
END;
$BODY$;";
oCheck.AddFunction(cSql);
cSql = @"CREATE OR REPLACE FUNCTION exc_validacao_contrato(cnpjcpf varchar,id_produto integer)
RETURNS text AS
$BODY$
declare
cSql text = '' ;
cJson text = '' ;
QrContrato record ;
Begin
cSql := 'select cad.* from cad_contrato ctr left join cad_cadastro cad on(ctr.cad_id = cad.cad_id) where cad.cad_cnpj_cpf = cnpjcpf and ctr.prd_id = id_produto' ;
For QrContrato in execute cSql do
End loop ;
return cJson;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;";
oCheck.AddFunction(cSql);
}