Fórum Somar valor de uma formula? Firebird 2.0 #58535

CREATE PROCEDURE ESSQLFUNC (
    id_empresa integer,
    b_calculo varchar(80),
    valor numeric(15,2),
    salario numeric(15,2),
    marcar varchar(10),
    quantidade varchar(10),
    vl_informado numeric(15,2),
    id_gerarformula integer,
    id_sal integer)
returns (
    salcontratformulas numeric(15,2),
    salgovformulas numeric(15,2),
    salariofixo numeric(15,2),
    salvlinformado numeric(15,2))
as
declare variable sqles varchar(1050) character set win1252;
begin
   SQLES = ´select A.id_empresa, A.salario, A.id_sal, B.id_formula, B.descricao, A.id_func, A.nome, B.id_gerarformula,
    case
      when B.marcar = ´´True´´ and B.B_Calculo = ´´Fixo´´ and B.valor = ´´False´´ Then
          A.salario
      when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Contratado´´ and B.quantidade = ´´True´´ and B.valor = ´´False´´  then
          cast(A.salario as varchar(10))||B.formula||´´*´´||B.quantia
      when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Contratado´´ and B.quantidade = ´´False´´ and B.valor = ´´False´´  then
          cast(A.salario as varchar (10))||B.formula
      when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Contratado´´ and B.quantidade = ´´False´´ and B.valor = ´´True´´  then
          B.vl_Informado
      when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Governo´´ and B.quantidade = ´´True´´ and B.valor = ´´False´´  then
          cast(A.salario as varchar(10))||B.formula||´´*´´||B.quantia
      when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Governo´´ and B.quantidade = ´´False´´ and B.valor = ´´False´´  then
          cast(A.salario as varchar (10))||B.formula
      when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Governo´´ and B.quantidade = ´´False´´ and B.valor = ´´True´´  then
          B.vl_informado
    end as CamposM_N
    from salario_m A left join formulas B on B.id_gerarformula = A.id_sal
    WHERE (B.marcar = ´´True´´)order by 1,6´;
    for execute statement
    SQLES
    into

    do
    begin
       SELECT COALESCE(SUM(A.Salario),0) FROM salario_m A INTO :SalarioFixo;
      suspend;
end^

begin
with dm.qRestrFolha do
  begin
    Sql.Text:=´select ´+dm.qRestrFolha.FieldByName(´CamposM_N´).asString+´ from rdb$database´;
    open;
  end;
end;

{$R *.dfm}
function ResultFormulas(Id_GerarFormula: Integer):Variant;
var
  sql: String;
begin
   dm.qRestrFolha.sql.clear;
   dm.qRestrFolha.sql.text:=´ select A.id_empresa, A.salario, A.id_sal, B.id_formula, B.descricao, A.id_func, A.nome, B.id_gerarformula, ´+
   ´ case ´+
     ´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Fixo´´ and B.valor = ´´False´´ Then ´+
         ´ A.salario ´+
     ´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Contratado´´ and B.quantidade = ´´True´´ and B.valor = ´´False´´  then ´+
         ´ cast(A.salario as varchar(10))||B.formula||´´*´´||B.quantia ´+
     ´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Contratado´´ and B.quantidade = ´´False´´ and B.valor = ´´False´´  then ´+
         ´ cast(A.salario as varchar (10))||B.formula ´+
     ´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Contratado´´ and B.quantidade = ´´False´´ and B.valor = ´´True´´  then ´+
         ´ B.vl_Informado ´+
     ´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Governo´´ and B.quantidade = ´´True´´ and B.valor = ´´False´´  then ´+
         ´ cast(A.salario as varchar(10))||B.formula||´´*´´||B.quantia ´+
     ´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Governo´´ and B.quantidade = ´´False´´ and B.valor = ´´False´´  then ´+
         ´ cast(A.salario as varchar (10))||B.formula ´+
     ´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Governo´´ and B.quantidade = ´´False´´ and B.valor = ´´True´´  then ´+
         ´ B.vl_informado ´+
   ´ end as CamposM_N ´+
   ´ from salario_m A left join formulas B on B.id_gerarformula = A.id_sal ´+
   ´ WHERE (A.id_empresa between :pEini and :pEfin) and (A.id_func between :pFini and :pFfin) and (B.marcar = ´´True´´)order by 1,6 ´; //--condiçao se nao tiver marcado gera restricao
   //--passando os parametros dos edits
   dm.qRestrFolha.parambyname(´pEini´).asInteger := EIni;
   dm.qRestrFolha.parambyname(´pEfin´).asInteger := EFin;
   dm.qRestrFolha.parambyname(´pFini´).asInteger := FIni;
   dm.qRestrFolha.parambyname(´pFfin´).asInteger := FFin;
   dm.qRestrFolha.open;
   if dm.qRestrFolha.RecordCount = 0 then begin
      Showmessage(´Formula Não Encontrada!´);
      dm.qRestrFolha.Close;
      dm.qRestrFolha.Free;
      Result := ´´;
      Exit;
   end;
   Sql :=´select (´+dm.qRestrFolha.FieldByName(´CamposM_N´).asString+´)as resultado from rdb$database´;

   dm.qRestrFolha.Close;
   dm.qRestrFolha.SQL.Text := sql;
   dm.qRestrFolha.Open;
   Result := dm.qRestrFolha.FieldByName(´RESULTADO´).Value;
   dm.qRestrFolha.Close;
   dm.qRestrFolha.Free;
end;

{$R *.dfm}
function Calcular(SMyExpression: string; digits: Byte): string;
  // Funçao para calcular uma simples operação matematica
var
  z: Char;
  ipos: Integer;

  function StrToReal(chaine: string): Real;
  var
    r: Real;
    Pos: Integer;
  begin
    Val(chaine, r, Pos);
    if Pos > 0 then Val(Copy(chaine, 1, Pos - 1), r, Pos);
    Result := r;
  end;

  function RealToStr(inreal: Extended; digits: Byte): string;
  var
    S: string;
  begin
    Str(inreal: 0: digits, S);
    realToStr := S;
  end;

  procedure NextChar;
  var
    s: string;
  begin
    if ipos > Length(SMyExpression) then
    begin
      z := #9;
      Exit;
    end
    else
    begin
      s := Copy(SMyExpression, ipos, 1);
      z := s[1];
      Inc(ipos);
    end;
    if z = ´ ´ then nextchar;
  end;

  function Expression: Real;
  var
    w: Real;

    function Factor: Real;
    var
      ws: string;
    begin
      Nextchar;
      if z in [´0´..´9´] then
      begin
        ws := ´´;
        repeat
          ws := ws + z;
          nextchar
        until not (z in [´0´..´9´, ´.´]);
        Factor := StrToReal(ws);
      end
      else if z = ´(´ then
      begin
        Factor := Expression;
        nextchar
      end
      else if z = ´+´ then Factor := +Factor
      else if Z = ´-´ then Factor := -Factor;
    end;

    function Term: Real;
    var
      W: Real;
    begin
      W := Factor;
      while Z in [´*´, ´/´] do
        if z = ´*´ then w := w * Factor
      else
        w := w / Factor;
      Term := w;
    end;
  begin
    w := term;
    while z in [´+´, ´-´] do
      if z = ´+´ then w := w + term
    else
      w := w - term;
    Expression := w;
  end;
begin
  ipos   := 1;
  Result := RealToStr(Expression, digits);
end;

Procedure TFAutoCalc.SQLFormulas; // A procedure
begin
FIni:=StrToInt(e3.text); //-variavel recebendo valor do edit FUNCIONARIOS
FFin:=StrToInt(e4.text); //-variavel recebendo valor do edit FUNCIONARIOS
dm.qRestrFolha.sql.clear;
dm.qRestrFolha.sql.text:=´ select A.id_empresa, A.salario, A.id_sal, B.id_formula, B.descricao, A.id_func, A.nome, B.id_gerarformula, ´+
´ case ´+
´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Fixo´´ and B.valor = ´´False´´ Then ´+
´ A.salario ´+
´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Contratado´´ and B.quantidade = ´´True´´ and B.valor = ´´False´´ then ´+
´ cast(A.salario as varchar(10))||B.formula||´´*´´||B.quantia ´+
´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Contratado´´ and B.quantidade = ´´False´´ and B.valor = ´´False´´ then ´+
´ cast(A.salario as varchar (10))||B.formula ´+
´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Contratado´´ and B.quantidade = ´´False´´ and B.valor = ´´True´´ then ´+
´ B.vl_Informado ´+
´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Governo´´ and B.quantidade = ´´True´´ and B.valor = ´´False´´ then ´+
´ cast(A.salario as varchar(10))||B.formula||´´*´´||B.quantia ´+
´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Governo´´ and B.quantidade = ´´False´´ and B.valor = ´´False´´ then ´+
´ cast(A.salario as varchar (10))||B.formula ´+
´ when B.marcar = ´´True´´ and B.B_Calculo = ´´Salario_Governo´´ and B.quantidade = ´´False´´ and B.valor = ´´True´´ then ´+
´ B.vl_informado ´+
´ end as CamposM_N ´+
´ from salario_m A left join formulas B on B.id_gerarformula = A.id_sal ´+
´ WHERE (A.id_func between :pFini and :pFfin) and (B.marcar = ´´True´´)order by 1,6 ´; //--condiçao se nao tiver marcado gera restricao
//--passando os parametros dos edits
dm.qRestrFolha.parambyname(´pFini´).asInteger := FIni;
dm.qRestrFolha.parambyname(´pFfin´).asInteger := FFin;
dm.qRestrFolha.open;
if dm.qRestrFolha.RecordCount = 0 then begin
Showmessage(´Formula Não Encontrada!´);
dm.qRestrFolha.Close;
dm.qRestrFolha.Free;
Exit;
end;
end;

procedure TFAutoCalc.Button1Click(Sender: TObject);
var
vresultado : String;
begin
SQLFormulas; // Procedure da SQL Formulas
listbox1.clear;
dm.qRestrFolha.DisableControls;
dm.qRestrFolha.First;
while not dm.qRestrFolha.Eof do
begin
//passa para o listbox o valor do dbgrid
vResultado := dm.qRestrFolha.FieldByName(´camposM_N´).AsString; //pegando o resultado da formula
vResultado := Calcular(VResultado, 2); //variavel global que recebe o resultado da função
Listbox1.Items.Add(dm.qRestrFolha.FieldByName(´camposM_N´).AsString+´  -  ´+vResultado); //passando o calculo da formula no listbox na segunda linha
dm.qRestrFolha.Next;
end;
dm.qRestrFolha.enablecontrols;

end;

CREATE PROCEDURE ESSQLFUNC 
returns (
    id_empresa integer,
    salario numeric(15,2),
    id_sal integer,
    id_formula integer,
    descricao varchar(100),
    id_func integer,
    nome varchar(50),
    id_gerarformula integer,
    calculo varchar(100),
    resultado numeric(15,2))
as
declare variable b_calculo varchar(50);
declare variable valor varchar(50);
declare variable quantidade varchar(50);
declare variable formula varchar(50);
declare variable quantia varchar(50);
declare variable vl_informado varchar(50);
begin
    for  
        select
            A.id_empresa, A.salario, A.id_sal, B.id_formula, B.descricao,
            A.id_func, A.nome, B.id_gerarformula, b.b_calculo, b.valor,
            b.quantidade, b.formula, b.quantia, b.vl_informado
        from
            salario_m A
        left join
            formulas B on B.id_gerarformula = A.id_sal
        where
            (B.marcar = ´True´)
        order by
            a.id_empresa, a.id_func
    into :id_empresa, :salario, :id_sal, :id_formula, :descricao,
         :id_func, :nome, :id_gerarformula, :b_calculo, :valor,
         :quantidade, :formula, :quantia, :vl_informado
    do
    begin
        if (b_calculo = ´Fixo´) then
            calculo = salario;
        else
        begin
            if (quantidade = ´True´) then
               calculo = cast(salario as varchar(10)) || formula || ´*´ || quantia;
            else
            begin
                if (valor = ´True´) then
                    calculo = vl_informado;
                else
                    calculo = cast(salario as varchar (10)) || formula;
            end
        end

        --- *** Aqui será executada a fórmula ***
        execute statement ´select ´ || calculo || ´ from rdb$database´ into resultado;

        suspend;
    end
end