unit UTransacaoDb;

interface

uses
  System.SysUtils, System.Variants, System.Classes,
  System.StrUtils,  Vcl.StdCtrls,

  {---Confirnados---}
  Vcl.Graphics, Vcl.Forms,  Vcl.Dialogs,  Vcl.ExtCtrls,
  {----------}

  Vcl.Imaging.pngimage,


  {---Banco----}
  FireDAC.Comp.Client,  Data.DB,  FireDAC.Stan.Param,
  {-----------}

  Soap.EncdDecd

  ;

  function ValidaLogin(sUser, sSenha  : string): Boolean;

  function ReadImgSystStream(sIdentifica  : string) :  TMemoryStream;


  {---------------------------------------}



  procedure SalvarImgBanco(sIdentifica,  sCaminhoImg  : string);



  {----------------------------------------}


  procedure SalvarMensagemBanco(MsgTipo: string; MsgIdentifica: string; MsgTexto: string; MsgAnexo: TMemoryStream  = nil);
  procedure AtualizarMensagemBanco(MsgTipo: string; MsgIDMenu:  string;  MsgTexto: string; MsgAnexo: TMemoryStream = nil);

  procedure AtualizarMsgMenuPrincipal(MsgTipo: string; MsgIdentifica:  string;  MsgTexto: WideString; MsgAnexo: TMemoryStream = nil);


  {================================}
  function IfExitClient(idTelefone:string): Boolean;
  function NewIDCliente: integer;
  function NewIDMsgWhats: integer;
  function SetUpdateMsgEnviada(idmsg: integer): boolean;

implementation

uses {
  Rsc.Factory.Connetion,} uDmMain;

function NewIDCliente: integer;
var
  aFdQuery  : TFDQuery;
begin
  Result  :=  -1;
  try
    aFdQuery := TFDQuery.Create(nil);
    try
      aFdQuery.Connection :=  DmMain.FDCon;
      with aFdQuery do
      begin
        close;
        sql.Clear;
        sql.Add('SELECT GEN_ID(GEN_WBOTCONTATO_ID, 1) FROM RDB$DATABASE ');
        Open;
        Result  :=  Fields[0].AsInteger;
      end;
    finally
      aFdQuery.Free;
    end;
  except on E: Exception do
    begin
      MessageDlg('Erro ao Gerar Novo ID: ' + #13 + e.Message, TMsgDlgType.mtError, [TMsgDlgBtn.mbOK], 0);
    end;
  end;
end;

function NewIDMsgWhats: integer;
var
  aFdQuery  : TFDQuery;
begin
  Result  :=  -1;
  try
    aFdQuery := TFDQuery.Create(nil);
    try
      aFdQuery.Connection :=  DmMain.FDCon;
      with aFdQuery do
      begin
        close;
        sql.Clear;
        sql.Add('SELECT GEN_ID(GEN_MSGWHATSAPP_ID, 1) FROM RDB$DATABASE ');
        Open;
        Result  :=  Fields[0].AsInteger;
      end;
    finally
      aFdQuery.Free;
    end;
  except on E: Exception do
    begin
      MessageDlg('Erro ao Gerar Novo ID: ' + #13 + e.Message, TMsgDlgType.mtError, [TMsgDlgBtn.mbOK], 0);
    end;
  end;
end;

function SetUpdateMsgEnviada(idmsg: integer): boolean;
var
  FDQry: TFDQuery;
begin
  FDQry := TFDQuery.Create(nil);
  try

    try

      FDQry.Connection  :=  DmMain.FDCon;
      FDQry.Close;
      FDQry.SQL.Clear;
      FDQry.SQL.Add('UPDATE WBOTMENSAGEMAENVIAR SET ENVIADA = ''S'' ');
      FDQry.SQL.Add('WHERE ID = :ID');
      FDQry.ParamByName('ID').AsInteger  :=  idmsg;
      FDQry.ExecSQL;

      Result  :=  True;

      {crei um log para saber se foi tudo ok}
    except on E: Exception do
      begin
        Result  :=  False;
        {crei um log para pegar esses erros}
      end;
    end;
  finally
    FDQry.Free;
  end;
end;

function IfExitClient(idTelefone:string): Boolean;
var
  aFdQuery  : TFDQuery;
begin
  try
    aFdQuery := TFDQuery.Create(nil);
    try
      aFdQuery.Connection :=  DmMain.FDCon;
      with aFdQuery do
      begin
        close;
        sql.Clear;
        sql.Add('SELECT TELEFONE FROM WBOTCONTATO WHERE TELEFONE = :TELEFONE ');
        ParamByName('TELEFONE').AsString     :=  idTelefone;
        Open;

        if not IsEmpty then
          Result  :=  True
        else
          Result  :=  False;
      end;
    finally
      aFdQuery.Free;
    end;
  except on E: Exception do
    begin
      MessageDlg('Erro ao Consultar se Cliente já está cadastrado: ' + #13 + e.Message, TMsgDlgType.mtError, [TMsgDlgBtn.mbOK], 0);
    end;
  end;
end;

{=======================================}


function ValidaLogin(sUser, sSenha  : string): Boolean;
var
  aFdQuery  : TFDQuery;
begin
  aFdQuery := TFDQuery.Create(nil);
  try
    aFdQuery.Connection :=  DmMain.FDCon;
    with aFdQuery do
    begin
      close;
      sql.Clear;
      sql.Add('select * from login ');
      sql.Add('WHERE nick = :nick and senhauser = :senhauser');
      ParamByName('nick').Value     :=  sUser;
      ParamByName('senhauser').Value  :=  sSenha;
      Open;

      if not IsEmpty then
        Result  :=  True
      else
        Result  :=  False;
    end;
  finally
    aFdQuery.Free;
  end;
end;

procedure AtualizarMsgMenuPrincipal(MsgTipo, MsgIdentifica: string; MsgTexto: WideString; MsgAnexo: TMemoryStream);
var
  aFdQuery  : TFDQuery;
begin
  aFdQuery := TFDQuery.Create(nil);
  try
    aFdQuery.Connection :=  DmMain.FDCon;
    with aFdQuery do
    begin
      close;
      sql.Clear;
      sql.Add('UPDATE tbmsgwhatsapp SET msg_id_menu = :msg_id_menu, msg_texto = :msg_texto,  msg_anexo = :msg_anexo  ');
      sql.Add('WHERE msg_tipo = :msg_tipo and msg_id = 1');
      ParamByName('msg_tipo').Value     :=  MsgTipo;
      ParamByName('msg_id_menu').Value  :=  MsgIdentifica;
      ParamByName('msg_texto').AsWideString    :=  MsgTexto;
      if MsgAnexo <> nil then
        ParamByName('msg_anexo').LoadFromStream(MsgAnexo, ftBlob);
      ExecSQL;
      ShowMessage('Atualização Realizada Com Sucesso');
    end;
  finally
    aFdQuery.Free;
  end;
end;

procedure AtualizarMensagemBanco(MsgTipo: string; MsgIDMenu:  string;  MsgTexto: string; MsgAnexo: TMemoryStream);
var
  aFdQuery  : TFDQuery;
begin
  aFdQuery := TFDQuery.Create(nil);
  try
    aFdQuery.Connection :=  DmMain.FDCon;
    with aFdQuery do
    begin
      close;
      sql.Clear;
      sql.Add('UPDATE tbmsgwhatsapp SET msg_texto = :msg_texto,  msg_anexo = :msg_anexo  ');
      sql.Add('WHERE msg_tipo = :msg_tipo and msg_id_menu = :msg_id_menu  ');
      ParamByName('msg_tipo').Value     :=  MsgTipo;
      ParamByName('msg_id_menu').Value  :=  MsgIDMenu;
      ParamByName('msg_texto').Value    :=  MsgTexto;
      ParamByName('msg_anexo').LoadFromStream(MsgAnexo, ftBlob);
      ExecSQL;
      ShowMessage('Atualização Realizada Com Sucesso');
    end;
  finally
    aFdQuery.Free;
  end;
end;

procedure SalvarMensagemBanco(MsgTipo: string; MsgIdentifica: string; MsgTexto: string; MsgAnexo: TMemoryStream);
var
  aFdQuery  : TFDQuery;
  NewIDMenu : Integer;
begin
  {$REGION 'Ver Novo ID Resposta'}
  aFdQuery := TFDQuery.Create(nil);
  try
    aFdQuery.Connection :=  DmMain.FDCon;
    with aFdQuery do
    begin
      close;
      sql.Clear;
      sql.Add('select * from tbmsgwhatsapp where msg_tipo  = :msg_tipo');
      ParamByName('msg_tipo').Value  :=  'resposta';
      Open;

      NewIDMenu :=  RecordCount + 1;
    end;
  finally
    aFdQuery.Free;
  end;
  {$ENDREGION}

  if NewIDMenu > 20 then
    begin
      ShowMessage('Desculpe! Mas Esta Limitado a Apenas 20 (Vinte) Respostas! ');
      Exit;
    end;

  aFdQuery := TFDQuery.Create(nil);
  try
    aFdQuery.Connection :=  DmMain.FDCon;
    with aFdQuery do
    begin
      close;
      sql.Clear;
      sql.Add('insert into tbmsgwhatsapp ( msg_tipo, msg_id_menu, msg_texto,  msg_anexo ) ');
      sql.Add('values ( :msg_tipo, :msg_id_menu, :msg_texto,  :msg_anexo )');
      ParamByName('msg_tipo').Value     :=  MsgTipo;
      ParamByName('msg_id_menu').Value  :=  MsgIdentifica;
      ParamByName('msg_texto').Value    :=  MsgTexto;
      if MsgAnexo <> nil then
        ParamByName('msg_anexo').LoadFromStream(MsgAnexo, ftBlob);
      ExecSQL;
      ShowMessage('Resposta Salva Com Sucesso');
    end;
  finally
    aFdQuery.Free;
  end;
end;

function  jaSalvo(aIdentifica : string) : Boolean;
var
  aFdQuery  : TFDQuery;
begin
  aFdQuery := TFDQuery.Create(nil);
  try
    aFdQuery.Connection :=  DmMain.FDCon;
    with aFdQuery do
    begin
      close;
      sql.Clear;
      sql.Add('select img_identifica from tbimgsystem  where  img_identifica  = :img_identifica');
      Params.ParamByName('img_identifica').Value  :=  aIdentifica;
      Open;
    end;
    if aFdQuery.Eof then
      Result  :=  False
    else
      Result  :=  True;
  finally
    aFdQuery.Free;
  end;
end;

procedure SalvarImgBanco(sIdentifica,  sCaminhoImg  : string);
var
  aMemStrmIn    : TMemoryStream;
  aStr          : TStream;
  aFdQuery      : TFDQuery;
  sNomeImg      : string;
  sExtencaoImg  : string;
begin
  sNomeImg      :=  '';
  sExtencaoImg  :=  '';

  if FileExists(sCaminhoImg) then
  begin
    sNomeImg  :=  ExtractFileName(sCaminhoImg);
    sExtencaoImg  :=  ExtractFileExt(sNomeImg);
    aMemStrmIn    := TMemoryStream.Create;
    aFdQuery  :=  TFDQuery.Create(nil);
    aStr  :=  TFileStream.Create(sCaminhoImg, fmOpenRead);
    try
      aStr.Position :=  0;
      aMemStrmIn.LoadFromStream(aStr);
      aMemStrmIn.Seek(0,  soFromBeginning);
      if aMemStrmIn.Size = 0 then
        Exit;

      aFdQuery.Connection :=  DmMain.FDCon;

      if jaSalvo(sIdentifica) then
      begin
        with aFdQuery do
        begin
              try
                close;
                sql.Clear;
                sql.Add('UPDATE tbimgsystem SET IMG_NOME  = :IMG_NOME,  IMG_EXTENSAO  = :IMG_EXTENSAO, IMG_MSTREAM = :IMG_MSTREAM  WHERE IMG_IDENTIFICA = :IMG_IDENTIFICA');
                params.ParamByName('IMG_NOME').Value  :=  sNomeImg;
                params.ParamByName('IMG_EXTENSAO').Value  :=  sExtencaoImg;
                params.ParamByName('IMG_MSTREAM').LoadFromStream(aMemStrmIn, ftBlob);
                params.ParamByName('IMG_IDENTIFICA').Value :=  sIdentifica;
                execSQL;
                ShowMessage('Imagem Atualizado Com Sucesso');
              except on E: Exception do
                ShowMessage('Erro ao Atualizar Imagem no Banco: ' + #13 + e.Message
                            + #13 + 'Erro: '  + e.ClassName);
              end;
            end;
          end
        else
          begin
            with aFdQuery do
            begin
              try
                close;
                sql.Clear;
                sql.Add('INSERT INTO tbimgsystem (IMG_IDENTIFICA,  IMG_NOME,  IMG_EXTENSAO, IMG_MSTREAM) ');
                sql.Add('  VALUES (:IMG_IDENTIFICA,  :IMG_NOME, :IMG_EXTENSAO,  :IMG_MSTREAM)');
                params.ParamByName('IMG_IDENTIFICA').Value :=  sIdentifica;
                params.ParamByName('IMG_NOME').Value  :=  sNomeImg;
                params.ParamByName('IMG_EXTENSAO').Value  :=  sExtencaoImg;
                params.ParamByName('IMG_MSTREAM').LoadFromStream(aMemStrmIn,  ftBlob);
                execSQL;
                ShowMessage('Imagem Inserida Com Sucesso');
          except on E: Exception do
            ShowMessage('Erro ao Inserir Imagem no Banco' + #13 + e.Message
                        + #13 + 'Erro: '  + e.ClassName);
          end;
        end;
      end;
    finally
      aMemStrmIn.Free;
      aStr.Free;
      aFdQuery.Free;
    end;
  end;
end;

function ReadImgSystStream(sIdentifica  : string):  TMemoryStream;
var
  vStream: TMemoryStream;
  aFdQuery      : TFDQuery;
begin
  aFdQuery := TFDQuery.Create(nil);
  vStream := TMemoryStream.Create;
  try
    try
      aFdQuery.Connection :=  DmMain.FDCon;
      with aFdQuery do
      begin
        close;
        sql.Clear;
        sql.Add('select * from tbimgsystem ');
        sql.Add(' where img_identifica = :img_identifica ');
        ParamByName('img_identifica').Value  :=  sIdentifica;
        open;

        if IsEmpty = false then
        begin
          TBlobField(aFdQuery.FieldByName('img_mstream')).SaveToStream(vStream);
          vStream.Position := 0;
//          result :=  vStream;
        end;
      end;
    except on E: Exception do
      ShowMessage('Um Erro Ocorreu ao Tentar Localizar a Imagem!'  + #13 + e.Message);
    end;
  finally
    result :=  vStream;
    vStream.Free;
    aFdQuery.Free;
  end;
end;



end.
