unit HYYearQueryUnit;

interface

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

type
  THYYearQueryFrm = class(TForm)
    BitBtn5: TBitBtn;
    BitBtn3: TBitBtn;
    GroupBox2: TGroupBox;
    PageControl1: TPageControl;
    Label37: TLabel;
    Label56: TLabel;
    ComboBox29: TComboBox;
    Label57: TLabel;
    ComboBox30: TComboBox;
    Label58: TLabel;
    ComboBox31: TComboBox;
    TabSheet2: TTabSheet;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label19: TLabel;
    ComboBox5: TComboBox;
    ComboBox6: TComboBox;
    Panel2: TPanel;
    Bevel5: TBevel;
    Label12: TLabel;
    Bevel6: TBevel;
    Bevel7: TBevel;
    Bevel8: TBevel;
    Label13: TLabel;
    Label14: TLabel;
    Label15: TLabel;
    Label16: TLabel;
    Label17: TLabel;
    Label18: TLabel;
    ComboBox7: TComboBox;
    ComboBox8: TComboBox;
    Edit3: TEdit;
    CheckBox1: TCheckBox;
    ComboBox9: TComboBox;
    CheckBox2: TCheckBox;
    ComboBox10: TComboBox;
    CheckBox3: TCheckBox;
    ComboBox11: TComboBox;
    ComboBox12: TComboBox;
    Edit4: TEdit;
    ComboBox13: TComboBox;
    ComboBox14: TComboBox;
    Edit5: TEdit;
    BitBtn2: TBitBtn;
    ImageList1: TImageList;
    Label20: TLabel;
    DBGrid1: TDBGrid;
    ADOTable1: TADOTable;
    DataSource1: TDataSource;
    BitBtn19: TBitBtn;
    BitBtn18: TBitBtn;
    BitBtn20: TBitBtn;
    Label1: TLabel;
    ComboBox1: TComboBox;
    Label2: TLabel;
    ComboBox2: TComboBox;
    Label3: TLabel;
    ComboBox3: TComboBox;
    Label4: TLabel;
    ComboBox4: TComboBox;
    Label5: TLabel;
    ComboBox17: TComboBox;
    Label6: TLabel;
    Label7: TLabel;
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure CheckBox3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure ComboBox7Change(Sender: TObject);
    procedure BitBtn18Click(Sender: TObject);
    procedure ComboBox11Change(Sender: TObject);
    procedure BitBtn19Click(Sender: TObject);
    procedure ComboBox13Change(Sender: TObject);
    procedure BitBtn20Click(Sender: TObject);
  private
    Object1:TRelation;
    function  checkData:boolean;
  public
    { Public declarations }
  end;

var
  HYYearQueryFrm: THYYearQueryFrm;

implementation

uses HYYearReportQuery;

{$R *.dfm}
function  THYYearQueryFrm.checkData:boolean;
var
tempnum:integer;
begin
         try
               tempnum:=StrToInt(ComboBox5.Text)+StrToInt(ComboBox6.Text);
         except
               MessageBox(self.Handle,'年份數據類型不對!','消息',MB_OK);
               CheckData:=false;
               exit;
         end;
         if((StrToInt(ComboBox5.Text)<2000) or (StrToInt(ComboBox6.Text)>3000))then
         begin
               MessageBox(self.Handle,'選擇年份不在2000-3000年之間!','消息',MB_OK);
               CheckData:=false;
               exit;
         end;
         if(StrToInt(ComboBox5.Text)>StrToInt(ComboBox6.Text))then
         begin
               MessageBox(self.Handle,'年份區間不合法!','消息',MB_OK);
               CheckData:=false;
               exit;
         end;  
end;
procedure THYYearQueryFrm.BitBtn2Click(Sender: TObject);
begin
         if(not checkData)then exit;
         HYYearRepQueryFrm.ADOQuery1.SQL.Clear;
         if (ComboBox5.Text<>'') and (ComboBox6.Text<>'') then
         HYYearRepQueryFrm.ADOQuery1.SQL.Add('select * from 公交行業統計年報表 where '+
                           ' 統計年份>='+ComboBox5.Text+
                           ' and 統計年份<='+ComboBox6.Text)
         else
         begin
              MessageBox(self.Handle,'輸入條件語法錯誤！請選擇正確的時間區間！','消息',MB_OK);
              exit;
         end;
         if(CheckBox1.Checked)then
         begin
               if ((ComboBox7.Text='') or (ComboBox8.Text='') or (Edit3.Text=''))then
               begin
                    MessageBox(self.Handle,'輸入條件語法錯誤！請重新選擇條件！','消息',MB_OK);
                    exit;
               end;
               if(not (HYYearRepQueryFrm.ADOQuery1.FieldByName(trim(ComboBox7.Text)).DataType  in [ftString,ftWideString,ftFixedChar]))then
               HYYearRepQueryFrm.ADOQuery1.SQL.Add(' and '+ComboBox7.Text+' '+ComboBox8.Text+' '+Edit3.Text)  else
               HYYearRepQueryFrm.ADOQuery1.SQL.Add(' and '+ComboBox7.Text+' '+ComboBox8.Text+' '+''''+trim(Edit3.Text)+'''');
         end ;
         if(CheckBox2.Checked)then
         begin
                if (ComboBox11.Text='') or (ComboBox12.Text='') or  (Edit4.Text='') then
                begin
                     MessageBox(self.Handle,'輸入條件語法錯誤！請重新選擇條件！','消息',MB_OK);
                     exit;
                end;
                if(not (HYYearRepQueryFrm.ADOQuery1.FieldByName(ComboBox11.Text).DataType  in [ftString,ftWideString,ftFixedChar]))then
                HYYearRepQueryFrm.ADOQuery1.SQL.Add(' '+ComboBox9.Text+' '+ComboBox11.Text+' '+ComboBox12.Text+' '+Edit4.Text)  else
                HYYearRepQueryFrm.ADOQuery1.SQL.Add(' '+ComboBox9.Text+' '+ComboBox11.Text+' '+ComboBox12.Text+' '+''''+trim(Edit4.Text)+'''');
         end ;
         if(CheckBox3.Checked)then
         begin
                if (ComboBox13.Text='') or (ComboBox14.Text='') or (Edit5.Text='') then
                begin
                     MessageBox(self.Handle,'輸入條件語法錯誤！請重新選擇條件！','消息',MB_OK);
                     exit;
                end;
                if(not (HYYearRepQueryFrm.ADOQuery1.FieldByName(ComboBox13.Text).DataType  in [ftString,ftWideString,ftFixedChar]))then
                HYYearRepQueryFrm.ADOQuery1.SQL.Add(' '+ComboBox10.Text+' '+ComboBox13.Text+' '+ComboBox14.Text+' '+Edit5.Text)  else
                HYYearRepQueryFrm.ADOQuery1.SQL.Add(' '+ComboBox10.Text+' '+ComboBox13.Text+' '+ComboBox14.Text+' '+''''+trim(Edit5.Text)+'''');
         end;

         if(ComboBox29.Text<>'')then
         begin
               HYYearRepQueryFrm.ADOQuery1.Active:=False;
               HYYearRepQueryFrm.ADOQuery1.SQL.Add(' order by '+ComboBox29.Text);
               if(ComboBox30.Text<>'')then
               begin
                    HYYearRepQueryFrm.ADOQuery1.SQL.Add(','+ComboBox30.Text);
                    if(ComboBox31.Text<>'')then
                    begin
                         HYYearRepQueryFrm.ADOQuery1.SQL.Add(','+ComboBox31.Text);
                    end;
               end;

         end;
         try
               HYYearRepQueryFrm.ADOQuery1.open;
               MessageBox(self.Handle,'條件正確','消息',MB_OK);
         except
               MessageBox(self.Handle,'條件不正確','消息',MB_OK);
         end;
end;

procedure THYYearQueryFrm.BitBtn5Click(Sender: TObject);
begin
         if(not checkData)then exit;
         HYYearRepQueryFrm.ADOQuery1.SQL.Clear;
         if (ComboBox5.Text<>'') and (ComboBox6.Text<>'') then
         begin
                HYYearRepQueryFrm.ADOQuery1.SQL.Add('select * from 公交行業統計年報表 where '+
                           ' 統計年份>='+ComboBox5.Text+
                           ' and 統計年份<='+ComboBox6.Text+