unit fcc_vidacomercial;

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, frxClass, frxExportPDF, frxDBSet,
  Data.DB,    Vcl.Grids,
  Vcl.DBGrids, Vcl.StdCtrls, Vcl.DBCtrls, Vcl.ExtCtrls, frxExportBaseDialog;

type
  Tcc_vidacomercial = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    Label3: TLabel;
    combocampanyas: TDBLookupComboBox;
    comboencargado: TDBLookupComboBox;
    bteimprimir: TButton;
    Panel2: TPanel;
    DBGrid1: TDBGrid;
    dscontroladores: TDataSource;
    qcontroladores: TFDQuery;
    dsqcampanyas: TDataSource;
    qlotes: TFDQuery;
    qcampanyas: TFDQuery;
    qcc_vidacomercial: TFDQuery;
    dsqccvidacomercial: TDataSource;
    dsqccvidacomercialinf: TDataSource;
    informedbencargado: TfrxDBDataset;
    informedbempresa: TfrxDBDataset;
    informedb: TfrxDBDataset;
    frxPDFExport1: TfrxPDFExport;
    informes: TfrxReport;
    qcc_vidacomercialinf: TFDQuery;
    qcampanyasid: TIntegerField;
    qcampanyasidempresa: TIntegerField;
    qcampanyasid_anterior: TIntegerField;
    qcampanyasdescripcion: TWideStringField;
    qcampanyasfechainicio: TDateField;
    qcampanyasfechafin: TDateField;
    qcampanyasult_pesada: TIntegerField;
    qcontroladoreside: TIntegerField;
    qcontroladoresid: TSmallintField;
    qcontroladoresnombre: TWideStringField;
    qcontroladoresfirma: TBlobField;
    qcc_vidacomercialide: TIntegerField;
    qcc_vidacomerciallote: TIntegerField;
    qcc_vidacomercialidenvase: TWideStringField;
    qcc_vidacomercialidformato: TIntegerField;
    qcc_vidacomercialalbaran: TWideStringField;
    qcc_vidacomercialfecha: TDateField;
    qcc_vidacomercialcliente: TWideStringField;
    qcc_vidacomercialdescenvase: TWideStringField;
    qcc_vidacomercialdescformato: TWideStringField;
    qcc_vidacomercialproducto: TWideStringField;
    qcc_vidacomercialdia_1: TWideStringField;
    qcc_vidacomercialdia_2: TWideStringField;
    qcc_vidacomercialdia_3: TWideStringField;
    qcc_vidacomercialdia_4: TWideStringField;
    qcc_vidacomercialdia_5: TWideStringField;
    qcc_vidacomercialdia_6: TWideStringField;
    qcc_vidacomercialdia_7: TWideStringField;
    qalbaran: TFDQuery;
    qalbaranidalbaran: TWideStringField;
    qalbarannomcliente: TWideStringField;
    qlotesid_envase: TWideStringField;
    qlotesdesc_envase: TWideStringField;
    qlotesid_formato: TIntegerField;
    qlotesdesc_formato: TWideStringField;
    qlotesfecha: TDateField;
    qlotesidempresa: TIntegerField;
    qlotesmprima: TWideStringField;
    qloteslote: TIntegerField;
    qcc_vidacomercialinfide: TIntegerField;
    qcc_vidacomercialinflote: TIntegerField;
    qcc_vidacomercialinfidenvase: TWideStringField;
    qcc_vidacomercialinfidformato: TIntegerField;
    qcc_vidacomercialinffecha: TDateField;
    qcc_vidacomercialinfalbaran: TWideStringField;
    qcc_vidacomercialinfcliente: TWideStringField;
    qcc_vidacomercialinfdescenvase: TWideStringField;
    qcc_vidacomercialinfdescformato: TWideStringField;
    qcc_vidacomercialinfproducto: TWideStringField;
    qcc_vidacomercialinfdia_1: TWideStringField;
    qcc_vidacomercialinfdia_2: TWideStringField;
    qcc_vidacomercialinfdia_3: TWideStringField;
    qcc_vidacomercialinfdia_4: TWideStringField;
    qcc_vidacomercialinfdia_5: TWideStringField;
    qcc_vidacomercialinfdia_6: TWideStringField;
    qcc_vidacomercialinfdia_7: TWideStringField;
    procedure FormShow(Sender: TObject);
    procedure dsqcampanyasDataChange(Sender: TObject; Field: TField);
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure bteimprimirClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  cc_vidacomercial: Tcc_vidacomercial;
const

  rojo=$00AAAAFF;
  amarillo=$00B9FFFF;
  verde=$00C7FBAA;
  azul=$00FFFFBB;


implementation

{$R *.dfm}

procedure Tcc_vidacomercial.bteimprimirClick(Sender: TObject);
begin
   qcc_vidacomercialinf.SQL.Clear;
   qcc_vidacomercialinf.SQL.Add('select * from ('+qcc_vidacomercial.sql.text+') as vidacomercial where dia_7 is not null order by fecha');
   qcc_vidacomercialinf.Open;
   informedb.Datasource:=dsqccvidacomercialinf;
   informes.Report.Clear;
   informes.LoadFromFile('c:\agrigest\informes\infccvidacomercial.fr3');
   informes.Variables.Variables['CAMPANYA']:=QUOTEDSTR(qcampanyasdescripcion.asstring);
   informes.ShowReport();
end;

procedure Tcc_vidacomercial.DBGrid1DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
     if (qcc_vidacomercialdia_7.AsString='') then
    begin
        DBGrid1.Canvas.brush.Color := rojo;
    end else
        DBGrid1.Canvas.brush.Color := verde;

    DBGrid1.DefaultDrawColumnCell(Rect, DataCol, Column, State);
end;


procedure Tcc_vidacomercial.dsqcampanyasDataChange(Sender: TObject;
  Field: TField);
var anyo,mes,dia:word;
    vanyo,vmes,vdia,vmessig,vfechainicio,vfechafin,variedades:string;
begin
    decodedate(qcampanyasfechainicio.AsDateTime,anyo,mes,dia);
    vanyo:=floattostr(anyo);
    vmes:=floattostr(mes);
    vdia:=floattostr(dia);
    vfechainicio:=''''+vanyo+'-'+vmes+'-'+vdia+'''';
    decodedate(qcampanyasfechafin.AsDateTime,anyo,mes,dia);
    vanyo:=floattostr(anyo);
    vmes:=floattostr(mes);
    vdia:=floattostr(dia);
    vfechafin:=''''+vanyo+'-'+vmes+'-'+vdia+'''';
    qlotes.SQL.Clear;
//    qlotes.SQL.Add('select distinct(lp.lote),ide,lp.id_envase,lp.desc_envase,id_formato,lp.desc_formato,ea.fecha,ea.idalbaran,al.nomcliente,ea.mprima from lotes_pesadas lp,ea_pesadas ea,albaranes al where lp.idea2=0');
//    qlotes.SQL.Add(' and lp.mprima not in (select id from materias_primas where desechos=1) and ea.fecha>='+vfechainicio+' and ea.fecha<='+vfechafin+' and lp.lote=ea.lote and lp.ide=ea.idempresa and ea.idalbaran in (select concat(serie,'+''''+'/'+''''+',id) from albaranes) and ea.idempresa=al.idempresa and ea.idalbaran=concat(al.serie,'+''''+'/'+''''+',al.id)');
//    qlotes.SQL.Add(' and (lp.lote,ide,lp.id_envase,lp.id_formato,ea.fechaaran) not in (select ide,lote,idenvase,idformato,albaran from cc_vidacomercial) order by lp.lote desc');
//    qlotes.Open;
    qlotes.SQL.Add('select distinct(lp.id_envase),lp.desc_envase,lp.id_formato,lp.desc_formato,ea.fecha,ea.idempresa,lp.mprima,lp.lote from ea_pesadas ea,lotes_pesadas lp,albaranes al where ea.idea=0 and');
    qlotes.SQL.Add(' ea.mprima not in (select id from materias_primas where desechos=1 or compuesto=1) and ea.fecha>='+vfechainicio+' and ea.fecha<='+vfechafin+' and lp.idea=ea.id and lp.idea2=ea.idea and lp.ide=ea.idempresa and ');
    qlotes.SQL.Add(' ea.idalbaran in (select concat(serie,'+''''+'/'+''''+',id) from albaranes) and ea.idempresa=al.idempresa and ea.idalbaran=concat(al.serie,'+''''+'/'+''''+',al.id) and');
    qlotes.SQL.Add(' (lp.lote,lp.ide,lp.id_envase,lp.id_formato,ea.fecha) not in (select lote,ide,idenvase,idformato,fecha from cc_vidacomercial) order by ea.fecha,lp.id_envase,lp.id_formato,lp.lote');
    qlotes.Open;
    qcc_vidacomercial.SQL.Clear;
    qcc_vidacomercial.SQL.Add('select * from cc_vidacomercial where fecha>='+vfechainicio+' and fecha<='+vfechafin+' order by lote desc');
    qcc_vidacomercial.Open;
    qalbaran.prepare;
    if qlotes.RecordCount>0 then
    begin
      while not qlotes.Eof do
      begin
         qalbaran.Close;
         qalbaran.ParamByName('FECHA').AsDate:=qlotesfecha.AsDatetime;
         qalbaran.ParamByName('PRODUCTO').Asstring:=qlotesmprima.Asstring;
         qalbaran.ParamByName('LOTE').Asinteger:=qloteslote.Asinteger;
         qalbaran.ParamByName('EMPRESA').Asinteger:=qlotesidempresa.Asinteger;
         qalbaran.ParamByName('ENVASE').Asstring:=qlotesid_envase.Asstring;
         qalbaran.ParamByName('FORMATO').Asinteger:=qlotesid_formato.Asinteger;
         qalbaran.Open;
         qcc_vidacomercial.Append;
         qcc_vidacomercialide.value:=qlotesidempresa.AsInteger;
         qcc_vidacomerciallote.value:=qloteslote.Asinteger;
         qcc_vidacomercialidenvase.value:=qlotesid_envase.Asstring;
         qcc_vidacomercialfecha.value:=qlotesfecha.Asdatetime;
         qcc_vidacomercialdescenvase.value:=qlotesdesc_envase.Asstring;
         qcc_vidacomercialidformato.value:=qlotesid_formato.Asinteger;
         qcc_vidacomercialdescformato.value:=qlotesdesc_formato.Asstring;
         qcc_vidacomercialalbaran.value:=qalbaranidalbaran.Asstring;
         qcc_vidacomercialcliente.value:=qalbarannomcliente.Asstring;
         qcc_vidacomercialproducto.value:=qlotesmprima.Asstring;
         qcc_vidacomercialdia_1.value:='C';
         qcc_vidacomercialdia_2.value:='C';
         qcc_vidacomercialdia_3.value:='C';
         qcc_vidacomercialdia_4.value:='C';
         qcc_vidacomercialdia_5.value:='C';
         qcc_vidacomercialdia_6.Value:='C';
         qcc_vidacomercial.Post;
         qlotes.Next;
      end;
    end;
end;

procedure Tcc_vidacomercial.FormShow(Sender: TObject);
begin
    qcampanyas.Open;
    qcontroladores.Open;
    qlotes.Open;
    combocampanyas.Keyvalue:=qcampanyasid.asstring;
    comboencargado.Keyvalue:=qcontroladoresnombre.asstring;
end;

end.