unit fcc_vidacomercial;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, frxClass, frxExportPDF, frxDBSet,
  Data.DB, ZAbstractDataset, ZDataset, ZAbstractRODataset, 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: TZReadOnlyQuery;
    dsqcampanyas: TDataSource;
    qlotes: TZQuery;
    qcampanyas: TZReadOnlyQuery;
    qcc_vidacomercial: TZQuery;
    dsqccvidacomercial: TDataSource;
    dsqccvidacomercialinf: TDataSource;
    informedbencargado: TfrxDBDataset;
    informedbempresa: TfrxDBDataset;
    informedb: TfrxDBDataset;
    frxPDFExport1: TfrxPDFExport;
    informes: TfrxReport;
    qcc_vidacomercialinf: TZQuery;
    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: TZQuery;
    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.