{
===============================================================================
  Copyright (©) 2002. Rodax Software.
===============================================================================
  Los contenidos de este fichero son propiedad de Rodax Software titular del
  copyright. Este fichero sólo podrá ser copiado, distribuido y utilizado,
  en su totalidad o en parte, con el permiso escrito de Rodax Software, o de
  acuerdo con los términos y condiciones establecidas en el acuerdo/contrato
  bajo el que se suministra.
  -----------------------------------------------------------------------------
  Web:  www.rodax-software.com
===============================================================================
  Fecha primera versión: 31-12-2003
  Versión actual: 1.0.1
  Fecha versión actual: 02-04-2004
===============================================================================
  Modificaciones:

    Fecha         Comentarios
    ---------------------------------------------------------------------------
    02-04-2004    Se ha eliminado el atributo SERIE (P8 MULTIEMPRESA)
===============================================================================
}

unit InformeTrimestralVentas;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, InformeBase, FR_IBXDB, FR_Shape, FR_DSet, FR_DBSet, FR_Class,
  DB, IBCustomDataSet, IBQuery, RdxEmpresaActiva;

type
  TdmInformeTrimestralVentas = class(TdmInformeBase)
    TablaCab: TfrDBDataSet;
    TablaDet: TfrDBDataSet;
    TablaTrimestres: TIBQuery;
    TablaFacturas: TIBQuery;
  private
    FCodigoTrimestre : Variant;
    FListaIVA : TStringList;
    FListaRE : TStringList;
    function RellenarListaIVA : boolean;
    function RellenarListaRE : boolean;
  protected
    procedure RellenarCabecera(Band: TfrBand); override;
    procedure PrepararConsultas; override;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
  published
    property CodigoTrimestre : variant read FCodigoTrimestre write FCodigoTrimestre;
  end;

var
  dmInformeTrimestralVentas: TdmInformeTrimestralVentas;

implementation

{$R *.dfm}

uses
  IBSQL, StrFunc, Mensajes, Constantes, BaseDatos, TablaEmpresas;

{ TdmInformeBase1 }

constructor TdmInformeTrimestralVentas.Create(AOwner: TComponent);
begin
  inherited;
  FNombreInforme := 'InformeTrimestralVentas.frf';
  FListaIVA := TStringList.Create;
  FListaRE := TStringList.Create;
end;

destructor TdmInformeTrimestralVentas.Destroy;
begin
  FreeAndNil(FListaIVA);
  FreeAndNil(FListaRE);
  inherited;
end;

procedure TdmInformeTrimestralVentas.PrepararConsultas;
var
  i : integer;
begin
  inherited;

  if (not RellenarListaIVA) or (not RellenarListaRE) then
    raise Exception.Create('No se puede realizar el informe');

  with TablaTrimestres do
  begin
    Database := FBaseDatos;
    Transaction := FTransaccion;
    SQL.Clear;
    SQL.Add('select EXTRACT (YEAR FROM FECHAINI) as ANO, NOMBRE, FECHAINI, FECHAFIN from TRIMESTRES ');
    SQL.Add('where CODIGOEMPRESA = :CODIGOEMPRESA');
    SQL.Add('and CODIGO = :CODIGO');
    ParamByName('CODIGOEMPRESA').AsInteger := EmpresaActiva.Codigo;
    ParamByName('CODIGO').AsString := FCodigoTrimestre;
    Prepare;
  end;

  with TablaFacturas do
  begin
    Database := FBaseDatos;
    Transaction := FTransaccion;
    SQL.Clear;
    SQL.Add('select EXTRACT (MONTH FROM FECHAFACTURA) as MES, EXTRACT (DAY FROM FECHAFACTURA) as DIA, FC.CODIGO, ');
    SQL.Add('NIFCIF, NOMBRE, TIPOFACTURA, DESCRIPCION, (BASEIMPONIBLE-IMPORTEDESCUENTO) as BASEIMPONIBLE, ');

    for i := 0 to 2 do
    begin
      if i < FListaIVA.Count then
        SQL.Add('case IVA when ' + FListaIVA.Strings[i] + ' then IMPORTEIVA else null end as IVA' + IntToStr(i) + ', ')
      else
        SQL.Add('0 as IVA' + IntToStr(i) + ', ');
    end;

    for i := 0 to 2 do
    begin
      if i < FListaRE.Count then
        SQL.Add('case RE when ' + FListaRE.Strings[i] + ' then IMPORTERE else null end as RE' + IntToStr(i) + ', ')
      else
        SQL.Add('0 as RE' + IntToStr(i) + ', ');
    end;

    //Para que el importe total de cada factura sea de 4 decimales y el total salga con exactitud
    SQL.Add('BASEIMPONIBLE - coalesce(importedescuento,0) + coalesce(importere,0) + coalesce(importeiva,0) as IMPORTETOTAL ');
    SQL.Add('from FACTURASCLIENTE FC, TIPOSFACTURAS TF ');
    SQL.Add('where TIPOFACTURA = TF.CODIGO ');
    SQL.Add('and CODIGOEMPRESA = :CODIGOEMPRESA');
    SQL.Add('and CODIGOTRIMESTRE = :CODIGO ');
    SQL.Add('order by FECHAFACTURA, FC.CODIGO');
    ParamByName('CODIGOEMPRESA').AsInteger := EmpresaActiva.Codigo;
    ParamByName('CODIGO').AsString := FCodigoTrimestre;
    Prepare;
  end;
end;

procedure TdmInformeTrimestralVentas.RellenarCabecera(Band: TfrBand);
var
  iCont : Integer;
  i : integer;
  Objeto : TfrView;
begin
  inherited;
  with Band do
  begin
    for iCont := 0 to Objects.Count - 1 do
    begin
      Objeto := Objects[iCont];

      if ((Objeto is TfrMemoView) and (Objeto.Name = 'Empresa')) then
      begin
         (Objeto as TfrMemoView).Memo.Clear;
         (Objeto as TfrMemoView).Memo.Add('Empresa: ' + EmpresaActiva.NombreComercial);
      end;

      if ((Objeto is TfrMemoView) and (Pos('eIVA', Objeto.Name) > 0)) then
      begin
         (Objeto as TfrMemoView).Memo.Clear;
         i := Pos('eIVA', Objeto.Name) + 4;
         i := StrToInt(System.Copy(Objeto.Name, i, 1));
         if (FListaIVA.Count-1) >= i then
          (Objeto as TfrMemoView).Memo.Add(FListaIVA.Strings[i] + '%');
      end;

      if ((Objeto is TfrMemoView) and (Pos('eRE', Objeto.Name) > 0)) then
      begin
         (Objeto as TfrMemoView).Memo.Clear;
         i := Pos('eRE', Objeto.Name) + 3;
         i := StrToInt(System.Copy(Objeto.Name, i, 1));
         if (FListaRE.Count-1) >= i then
          (Objeto as TfrMemoView).Memo.Add(FListaRE.Strings[i] + '%');
      end;
    end;
  end;
end;

function TdmInformeTrimestralVentas.RellenarListaIVA : boolean;
var
  oSQL : TIBSQL;
begin
  Result := False;

  oSQL := TIBSQL.Create(Self);
  with oSQL do
  begin
    Database := dmBaseDatos.BD;
    Transaction := dmBaseDatos.Transaccion;
    SQL.Add('select distinct IVA');
    SQL.Add('from FACTURASCLIENTE FC');
    SQL.Add('where CODIGOEMPRESA = :CODIGOEMPRESA');
    SQL.Add('and CODIGOTRIMESTRE = :CODIGO');
    SQL.Add('order by IVA desc');
    ParamByName('CODIGOEMPRESA').AsInteger := EmpresaActiva.Codigo;
    ParamByName('CODIGO').AsString := FCodigoTrimestre;

    try
      Prepare;
      ExecQuery;
      FListaIVA.Clear;
      while not EOF do begin
        if not VarIsNull(Fields[0].AsVariant) then
          FListaIVA.Append(StringReplace(Fields[0].AsString, ',', '.', []));
        Next;
      end;
      result := True;
    finally
      Close;
      Transaction := NIL;
      Free;
    end;
  end;
end;

function TdmInformeTrimestralVentas.RellenarListaRE : boolean;
var
  oSQL : TIBSQL;
begin
  Result := False;

  oSQL := TIBSQL.Create(Self);
  with oSQL do
  begin
    Database := dmBaseDatos.BD;
    Transaction := dmBaseDatos.Transaccion;
    SQL.Add('select distinct RE');
    SQL.Add('from FACTURASCLIENTE FC');
    SQL.Add('where CODIGOEMPRESA = :CODIGOEMPRESA');
    SQL.Add('and CODIGOTRIMESTRE = :CODIGO');
    SQL.Add('order by RE desc');
    ParamByName('CODIGOEMPRESA').AsInteger := EmpresaActiva.Codigo;
    ParamByName('CODIGO').AsString := FCodigoTrimestre;

    try
      Prepare;
      ExecQuery;
      FListaRE.Clear;
      while not EOF do begin
        if not VarIsNull(Fields[0].AsVariant) then
          FListaRE.Append(StringReplace(Fields[0].AsString, ',', '.', []));
        Next;
      end;
      Result := True;
    finally
      Close;
      Transaction := NIL;
      Free;
    end;
  end;
end;


end.