unit fproductosfitosanitarios;

interface

uses
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.Stan.Async, FireDAC.DApt, FireDAC.Comp.DataSet, FireDAC.Comp.Client, uFireDacHelper,

  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, Data.Win.ADODB, Vcl.Grids,
  Vcl.DBGrids,    Vcl.StdCtrls,shellapi,
  Vcl.ExtCtrls,URLMon, Vcl.DBCtrls;

type
  Tproductosfitosanitarios = class(TForm)
    Panel7: TPanel;
    btAreutilizar: TButton;
    qfitosanitarios: TFDQuery;
    DBGrid1: TDBGrid;
    dsqfitosanitarios: TDataSource;
    qfitosanitariosregistro: TWideStringField;
    qfitosanitariosnombre_comercial: TWideStringField;
    qfitosanitariostitular: TWideStringField;
    qfitosanitariosmateria_activa: TWideStringField;
    qfitosanitariosautorizado: TIntegerField;
    qfitosanitariosfecha_actualizacion: TDateField;
    qfitosanitariostemp: TFDQuery;
    listado: TADOQuery;
    clistado: TADOConnection;
    qaux: TFDQuery;
    qfitosanitariostempregistro: TWideStringField;
    qfitosanitariostempnombre_comercial: TWideStringField;
    qfitosanitariostemptitular: TWideStringField;
    qfitosanitariostempmateria_activa: TWideStringField;
    gcrpagado: TDBCheckBox;
    Label1: TLabel;
    Edit1: TEdit;
    Button1: TButton;
    procedure btAreutilizarClick(Sender: TObject);
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure FormShow(Sender: TObject);
    procedure Edit1Change(Sender: TObject);
    procedure FormKeyPress(Sender: TObject; var Key: Char);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  productosfitosanitarios: Tproductosfitosanitarios;
const
  rojo=$00AAAAFF;
  amarillo=$00B9FFFF;
  verde=$00C7FBAA;
  azul=$00FFFFBB;

implementation

{$R *.dfm}

function borrarespacios(Const Search: String): String;
 Const BlackSpace = [#33..#126];
     Var Index: Byte;
Begin
  Index := 1;
  While (Index <= Length(Search)) and
        Not (Search[Index] in BlackSpace) Do
    Index := Index + 1;
  result := copy(Search,Index,255);
  Index := Length(Result);
  While (Index >0) and
        not (Result[Index] in BlackSpace) Do
    Index := Index -1;
  result := copy(Result,1,Index);

End;
procedure Tproductosfitosanitarios.btAreutilizarClick(Sender: TObject);
begin
    qfitosanitarios.Close;
    qaux.sql.clear;
    qaux.SQL.Add('delete from productos_fitosanitarios');
    qaux.ExecSQL;
    qaux.Close;
    qaux.sql.Clear;
    qaux.SQL.Add('insert into productos_fitosanitarios (registro,nombre_comercial,titular,materia_activa) (select * from productos_fitosanitariostemp pft where pft.registro not in (select registro from productos_fitosanitarios))');
    qaux.ExecSQL;
    qaux.Close;
    qaux.sql.Clear;
    qaux.SQL.Add('update productos_fitosanitarios set autorizado=0 where registro not in (select registro from productos_fitosanitariostemp)');
    qaux.ExecSQL;
    qaux.Close;
    qaux.sql.Clear;
    qaux.SQL.Add('update productos_fitosanitarios set fecha_actualizacion=:FECHA');
    qaux.prepare;
    qaux.parambyname('FECHA').asdate:=date;
    qaux.ExecSQL;
    qfitosanitarios.Open;


end;

procedure Tproductosfitosanitarios.Button1Click(Sender: TObject);
var direccion:pwidechar;
    registro,cadena:string;
begin
    registro:= qfitosanitariosregistro.AsString;
    registro:=borrarespacios(registro);
    cadena:='https://www.mapa.gob.es/agricultura/pags/fitos/registro/productos/pdf/'+registro+'.pdf';
    direccion:=pwidechar(widestring(cadena));
    URLDownloadToFile(nil, direccion, 'C:\agrigest\tmp\ficha.pdf', 0, nil);
    ShellExecute(Handle,nil,PChar('c:\agrigest\tmp\ficha.pdf'),'','',SW_SHOWNORMAL);
end;

procedure Tproductosfitosanitarios.DBGrid1DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
const IsChecked : array[Boolean] of Integer =
 (DFCS_BUTTONCHECK, DFCS_BUTTONCHECK or DFCS_CHECKED);
var
  DrawState: Integer;
  DrawRect: TRect;
begin
    if qfitosanitariosautorizado.Asinteger=1 then
    begin
        dbgrid1.Canvas.brush.Color := verde;
    end;
    if qfitosanitariosautorizado.Asinteger=0 then
    begin
        dbgrid1.Canvas.brush.Color := rojo;
    end;

    dbgrid1.DefaultDrawColumnCell(Rect, DataCol, Column, State);
if (gdFocused in State) then
begin

    if (Column.Field.FieldName = 'autorizado') then
     begin
       gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
       gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
       gcrpagado.Width := Rect.Right - Rect.Left;
       gcrpagado.Height := Rect.Bottom - Rect.Top;
       gcrpagado.Visible := True;
     end;
end else
     begin

       if (Column.Field.FieldName = 'autorizado') then
       begin
         DrawRect:=Rect;
         InflateRect(DrawRect,-1,-1);
         if column.Field.Asinteger=0  then DrawState := ischecked[false];
         if column.Field.Asinteger=1  then DrawState := ischecked[true];
      //   DrawState := ISChecked[Column.Field.Asboolean];
         DBgrid1.Canvas.FillRect(Rect);
         DrawFrameControl(DBgrid1.Canvas.Handle, DrawRect,
         DFC_BUTTON, DrawState);
       end;
    end;
 gcrpagado.visible:=false;


end;

procedure Tproductosfitosanitarios.DBGrid1TitleClick(Column: TColumn);
var
  st:TFDQuerySortType;
begin
  st:=qfitosanitarios.SortType;
  qfitosanitarios.SortedFields:=Column.FieldName;
  If st = stAscending then qfitosanitarios.SortType:=stDescending else qfitosanitarios.SortType:=stAscending;
  dsqfitosanitarios.DataSet.First;
end;

procedure Tproductosfitosanitarios.Edit1Change(Sender: TObject);
begin
    if edit1.Text<>'' then
    begin
       qfitosanitarios.Close;
       qfitosanitarios.Prepare;
       qfitosanitarios.ParamByName('FILTRO').AsString:='%'+edit1.Text+'%';
       qfitosanitarios.Open;
    end else
    begin
      qfitosanitarios.Close;
      qfitosanitarios.Prepare;
      qfitosanitarios.ParamByName('FILTRO').AsString:='%';
      qfitosanitarios.Open;
   end;

end;

procedure Tproductosfitosanitarios.FormKeyPress(Sender: TObject; var Key: Char);
begin
   key:=upcase(key);
end;

procedure Tproductosfitosanitarios.FormShow(Sender: TObject);
begin
    qfitosanitarios.Close;
    qfitosanitarios.Prepare;
    qfitosanitarios.ParamByName('FILTRO').AsString:='%';
    qfitosanitarios.Open;
end;

end.