PC Format Collection 48/SENT14D.ISO/tech/delphi/disk15/stocks.pak/BROWSER.PAS

Pascal/Delphi Source File | 1995-08-24 | 6.6 KB | 268 lines

unit Browser;

interface

uses WinTypes, WinProcs, Classes, Graphics, Forms, Controls, Buttons,
  StdCtrls, DBCtrls, Grids, DBGrids, ExtCtrls, DB, DBTables, Report;

type
  TfmMktBrowser = class(TForm)
    OKBtn: TBitBtn;
    CancelBtn: TBitBtn;
    HelpBtn: TBitBtn;
    Panel1: TPanel;
    Grid: TDBGrid;
    DropIndustry: TComboBox;
    Label1: TLabel;
    DropRating: TComboBox;
    Label2: TLabel;
    DropRecommend: TComboBox;
    DropRisk: TComboBox;
    Label4: TLabel;
    Label3: TLabel;
    GridQuery: TQuery;
    DataSource: TDataSource;
    TempQuery: TQuery;
    ButPrint: TBitBtn;
    Report: TReport;
    procedure DropClick(Sender: TObject);
    procedure ButPrintClick(Sender: TObject);
  private
    IndustCode: string[40];
    procedure PopulateCombos;
    procedure UpdateGridQuery;
  public
    procedure ShowBrowser;
  end;

var
  fmMktBrowser: TfmMktBrowser;

implementation

{$R *.DFM}

uses Dialogs,SysUtils;

const
  AllStr = 'All';
  FldRecommend = 'RCMNDATION';
  FldRating = 'RATING';
  FldRisk = 'RISK';
  FldIndustry = 'INDUSTRY';

procedure TfmMktBrowser.PopulateCombos;

  procedure PopulateRatingCombo;
  begin
    DropRating.Items.Clear;
    DropRating.Items.Add(AllStr);
    try
      TempQuery.Close;
      TempQuery.SQL.Clear;
      TempQuery.SQL.Add('Select distinct '+FldRating+' from MASTER');
      TempQuery.Open;
      while not TempQuery.EOF do
      begin
        DropRating.Items.Add(TempQuery.Fields[0].AsString);
        TempQuery.Next;
      end;
      DropRating.ItemIndex := 0;
    except
      on EDatabaseError do
        MessageDlg('Error determining Industry categories', mtError, [mbOK], 0);
    end;
  end;

  procedure PopulateRecommendCombo;
  begin
    DropRecommend.Items.Clear;
    DropRecommend.Items.Add(AllStr);
    try
      TempQuery.Close;
      TempQuery.SQL.Clear;
      TempQuery.SQL.Add('Select distinct '+FldRecommend+' from MASTER');
      TempQuery.Open;
      while not TempQuery.EOF do
      begin
        DropRecommend.Items.Add(TempQuery.Fields[0].AsString);
        TempQuery.Next;
      end;
      DropRecommend.ItemIndex := 0;
    except
      on EDatabaseError do
        MessageDlg('Error determining Industry categories', mtError, [mbOK], 0);
    end;
  end;

  procedure PopulateRiskCombo;
  begin
    DropRisk.Items.Clear;
    DropRisk.Items.Add(AllStr);
    try
      TempQuery.Close;
      TempQuery.SQL.Clear;
      TempQuery.SQL.Add('Select distinct '+FldRisk+' from MASTER');
      TempQuery.Open;
      while not TempQuery.EOF do
      begin
         DropRisk.Items.Add(TempQuery.Fields[0].AsString);
         TempQuery.Next;
      end;
      DropRisk.ItemIndex := 0;
    except
      on EDatabaseError do
        MessageDlg('Error determining Industry categories', mtError, [mbOK], 0);
    end;
  end;

  procedure PopulateIndustryCombo;
  begin
    DropIndustry.Items.Clear;
    DropIndustry.Items.Add(AllStr);
    try
      TempQuery.Close;
      TempQuery.SQL.Clear;
      TempQuery.SQL.Add('Select LONG_NAME from INDUSTRY');
      TempQuery.Open;
      while not TempQuery.EOF do
      begin
         DropIndustry.Items.Add(TempQuery.Fields[0].AsString);
         TempQuery.Next;
      end;
      DropIndustry.ItemIndex := 0;
    except
      on EDatabaseError do
        MessageDlg('Error determining Industry categories', mtError, [mbOK], 0);
    end;
  end;

begin
  TempQuery.Close;
  TempQuery.DatabaseName := 'StockDB';
  PopulateRatingCombo;
  PopulateRecommendCombo;
  PopulateRiskCombo;
  PopulateIndustryCombo;
end;

procedure TfmMktBrowser.UpdateGridQuery;
var
  WhereUsed: Boolean;

  function SQLPrefix:string;
  begin
    if WhereUsed then
      SQLPrefix := 'and '
    else
    begin
      WhereUsed := true;
      SQLPrefix := 'where '
    end;
  end;

begin
  Screen.Cursor := crHourGlass;
  WhereUsed := false;
  with GridQuery do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select * from MASTER');
    if DropIndustry.ItemIndex > 0 then
    begin
      { lookup the industry code from the INDUSTRY table }
      TempQuery.Close;
      TempQuery.SQL.Clear;
      TempQuery.SQL.Add('Select IND_CODE from INDUSTRY');
      TempQuery.SQL.Add('where LONG_NAME ="'+DropIndustry.Items[DropIndustry.ItemIndex]+'"');
      TempQuery.Open;
      { store the industry code; used for RS report }
      IndustCode := TempQuery.Fields[0].AsString;
      { now add the clause to the Grid SQL }
      SQL.Add(SQLPrefix+FldIndustry+' ='+IndustCode);
    end;
    if DropRating.ItemIndex > 0 then
      SQL.Add(SQLPrefix+FldRating+' ="'+DropRating.Items[DropRating.ItemIndex]+'"');
    if DropRecommend.ItemIndex > 0 then
      SQL.Add(SQLPrefix+FldRecommend+' ="'+DropRecommend.Items[DropRecommend.ItemIndex]+'"');
    if DropRisk.ItemIndex > 0 then
      SQL.Add(SQLPrefix+FldRisk+' ="'+DropRisk.Items[DropRisk.ItemIndex]+'"');
    SQL.Add('order by CO_NAME');
    Open;
  end;
  Screen.Cursor := crDefault;
end;

procedure TfmMktBrowser.ShowBrowser;
begin
  if not Visible then
  begin
    PopulateCombos;
    UpdateGridQuery;
  end;
  Show;
end;

procedure TfmMktBrowser.DropClick(Sender: TObject);
begin
  UpdateGridQuery;
end;

{ the report variables passed to ReportSmith are actually
  SQL clauses }
procedure TfmMktBrowser.ButPrintClick(Sender: TObject);
const
  NotNull = 'IS NOT NULL>';
  IndustVal = '@INDUST=<';
  RatingVal = '@RATING=<';
  RiskVal = '@RISK=<';
  RecmVal = '@RECM=<';

  function IndustValue:string;
  begin
    if DropIndustry.ItemIndex = 0 then
      IndustValue := IndustVal+NotNull
    else
      IndustValue := IndustVal+'='+InDustCode+'>';
  end;

  function RatingValue:string;
  begin
    if DropRating.ItemIndex = 0 then
      RatingValue := RatingVal+NotNull
    else
      RatingValue := RatingVal+'='''+DropRating.Items[DropRating.ItemIndex]+'''>';
  end;

  function RiskValue:string;
  begin
    if DropRisk.ItemIndex = 0 then
      RiskValue := RiskVal+NotNull
    else
      RiskValue := RiskVal+'='''+DropRisk.Items[DropRisk.ItemIndex]+'''>';
  end;

  function RecmValue:string;
  begin
    if DropRecommend.ItemIndex = 0 then
      RecmValue := RecmVal+NotNull
    else
      RecmValue := RecmVal+'='''+DropRecommend.Items[DropRecommend.ItemIndex]+'''>';
  end;

begin
  with Report do
  begin
    ReportDir := ExtractFilePath(Application.ExeName);
    InitialValues.Clear;
    InitialValues.Add(IndustValue);
    InitialValues.Add(RatingValue);
    InitialValues.Add(RiskValue);
    InitialValues.Add(RecmValue);
    Run;
  end;
end;

end.