unit Question1_UXXXX;

  {PROPOSED SOLUTION}

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids, ExtCtrls, Buttons;

type
  TfrmQuestionOne = class(TForm)
    qryQOne: TADOQuery;
    dsrOne: TDataSource;
    grdOne: TDBGrid;
    Panel2: TPanel;
    btnB: TButton;
    btnE: TButton;
    btnA: TButton;
    btnD: TButton;
    btnF: TButton;
    bmbClose: TBitBtn;
    btnC: TButton;
    btnG: TButton;

    procedure btnAClick(Sender: TObject);
    procedure btnBClick(Sender: TObject);
    procedure btnCClick(Sender: TObject);
    procedure btnDClick(Sender: TObject);
    procedure btnEClick(Sender: TObject);
    procedure btnFClick(Sender: TObject);
    procedure btnGClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmQuestionOne: TfrmQuestionOne;

implementation

{$R *.dfm}


procedure TfrmQuestionOne.btnAClick(Sender: TObject);
begin
  qryQOne.Active := False;
  qryQOne.SQL.Text :=  'SELECT SchoolName, Category, Balance, LastPayment ' +
                       'FROM tblSchools ORDER BY Category, LastPayment';
  qryQOne.Active := True;
end;

procedure TfrmQuestionOne.btnBClick(Sender: TObject);
begin
  qryQOne.Active := False;
  qryQOne.SQL.Text :=  'SELECT SchoolName, Balance, LastPayment FROM tblSchools ' +
                     'WHERE (Balance > 0) AND (#2011/11/25#-lastpayment >60)';
//     'WHERE (Balance > 0) AND (DateDiff("d",[LastPayment],"2011/11/25")>60)';
  qryQOne.Active := True;
end;

procedure TfrmQuestionOne.btnCClick(Sender: TObject);
begin
  qryQOne.Active := False;
  qryQOne.SQL.Text :=  'SELECT Officer, Count(LogTime) AS [Peek Hours] ' +
                       'FROM tblCallouts ' +
                       'WHERE (HOUR(LogTime) Between 15 and 24)  ' +
                       'GROUP BY Officer';
  qryQOne.Active := True;
end;

procedure TfrmQuestionOne.btnDClick(Sender: TObject);
begin
  qryQOne.Active := False;
  qryQOne.SQL.Text :=  'SELECT SchoolName, Reason , ' +
                       'Count(LogDate) AS [Number of visits] ' +
                       'FROM tblSchools, tblCallouts  ' +
                  'WHERE (tblSchools.SchoolNumber = tblCallouts.SchoolNumber) ' +
                       ' AND (Year(LogDate) = 2011)   ' +
                       'Group by SchoolName, Reason ';
  qryQOne.Active := True;
end;

procedure TfrmQuestionOne.btnEClick(Sender: TObject);
begin
  qryQOne.Active := False;
  qryQOne.SQL.Text :=  'SELECT '+
                       'Format(SUM(Installments),"Currency") AS [Total amount],  ' +
                       'Format(AVG(Balance),"Currency") AS [Average amount] ' +
                       'FROM tblSchools ' +
                       'WHERE (Category = "S")';
  qryQOne.Active := True;
  grdOne.Columns[0].Width := 100;
end;

procedure TfrmQuestionOne.btnFClick(Sender: TObject);
begin
  qryQOne.Active := False;
  qryQOne.SQL.Text :=  'UPDATE tblSchools  ' +
                       'SET Balance = Balance - Installments, LastPayment = #25/11/2011# ' +
                       'WHERE Category = "P" ';
  qryQOne.ExecSQL;
  MessageDlg('Records Processed Successfully',mtInformation,[mbok],0);
  //btnA.Click;      To execute option A via coding
end;

procedure TfrmQuestionOne.btnGClick(Sender: TObject);
begin
  qryQOne.Active := False;
  qryQOne.SQL.Text :=  'DELETE FROM tblCallouts ' +
                       'WHERE Year(LogDate) = 2010';
  qryQOne.ExecSQL;
  MessageDlg('Records Processed Successfully',mtInformation,[mbok],0);
  //btnC.Click;
end;

end.
