find.pas

Delphi编程┃Delphi数据库编程

unit find;

interface

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

type
  TFindForm = class(TForm)
    Panel1: TPanel;
    FindDataListBox: TListBox;
    Panel2: TPanel;
    Splitter1: TSplitter;
    Panel3: TPanel;
    Panel5: TPanel;
    Label9: TLabel;
    SFindDateTime: TDateTimePicker;
    DateCheckBox: TCheckBox;
    EFindDateTime: TDateTimePicker;
    DateFindRadioButton: TRadioButton;
    OtherFindRadioButton: TRadioButton;
    OtherComboBox: TComboBox;
    OtherEdit: TEdit;
    Panel4: TPanel;
    FindMasterStringGrid: TStringGrid;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    Panel6: TPanel;
    FindSpeedButton: TSpeedButton;
    FindListDBGrid: TDBGrid;
    FindMoreDBGrid: TDBGrid;
    DelSpeedButton: TSpeedButton;
    ModiSpeedButton: TSpeedButton;
    Panel9: TPanel;
    Label35: TLabel;
    SpeedButton7: TSpeedButton;
    FindSmallEdit: TEdit;
    FindMaxEdit: TEdit;
    Panel7: TPanel;
    PrintSpeedButton: TSpeedButton;
    PrintStyleComboBox: TComboBox;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FindSpeedButtonClick(Sender: TObject);
    procedure OtherComboBoxChange(Sender: TObject);
    procedure SFindDateTimeChange(Sender: TObject);
    procedure FindDataListBoxClick(Sender: TObject);
    procedure DelSpeedButtonClick(Sender: TObject);
    procedure FindListDBGridDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DateCheckBoxClick(Sender: TObject);
    procedure ModiSpeedButtonClick(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure FindListDBGridKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure FormCreate(Sender: TObject);
    procedure SpeedButton7Click(Sender: TObject);
    procedure PageControl1Change(Sender: TObject);
    procedure FindMoreDBGridDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FindForm: TFindForm;

implementation

uses dataform, main;

{$R *.dfm}

procedure TFindForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
  MDataForm.FindClientADOQuery.Close;
end;

procedure TFindForm.FindSpeedButtonClick(Sender: TObject);
begin
  try
    if DateFindRadioButton.Checked then
    begin
      with MDataForm.FindADOQuery do
      begin
        Close;
        SQL.Clear;
        if DateCheckBox.Checked then
        begin
          SQL.Add('select 清单编号 from MasterData where 录入日期>=:sd and 录入日期<=:ed');
          Parameters.ParamByName('sd').Value := FormatDateTime('yyyy-mm-dd',SFindDateTime.Date);
          Parameters.ParamByName('ed').Value := FormatDateTime('yyyy-mm-dd',EFindDateTime.Date);
        end else
        begin
          SQL.Add('select 清单编号 from MasterData where 录入日期=:sd');
          Parameters.ParamByName('sd').Value := FormatDateTime('yyyy-mm-dd',SFindDateTime.Date);
        end;
        Open;
      end;
    end else
    begin; //开始其它查询
      with MDataForm.FindADOQuery do
      begin
        Close;
        SQL.Clear;
        if (OtherComboBox.Text='品种名称') or (OtherComboBox.Text='客户名称') then
        begin
          SQL.Add('select 清单编号 from MasterData where '+
                  OtherComboBox.text+' like :z');
          Parameters.ParamByName('z').value := '%'+OtherEdit.text+'%';
        end else
        begin
          SQL.Add('select 清单编号 from MasterData where '+
                  OtherComboBox.text+' = :z');
          Parameters.ParamByName('z').value := StrToInt(OtherEdit.text);
        end;
        Open;
      end;
    end; //查询完毕

    with MDataForm.FindADOQuery do   //开始添加数据
    begin
      FindDataListBox.Items.Clear;
      while not Eof do
      begin
        FindDataListBox.Items.Add(FieldByName('清单编号').AsString);
        Next;
      end;
      Close;
    end;
  except
    MainForm.PshowMessage('系统错误','查询系统数据库时失败！',0);
    MDataForm.FindADOQuery.Close;
  end;
end;

procedure TFindForm.OtherComboBoxChange(Sender: TObject);
begin
  OtherFindRadioButton.Checked := true;
end;

procedure TFindForm.SFindDateTimeChange(Sender: TObject);
begin
  DateFindRadioButton.Checked := true;
end;

procedure TFindForm.FindDataListBoxClick(Sender: TObject);
var SelNo :string;
    ShowI :integer;
begin
  SelNo := FindDataListBox.Items[FindDataListBox.itemindex];  //根据流水号查询
  try
    with MDataForm.FindMasterADOQuery do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select * from MasterData where 清单编号=:no');
      Parameters.ParambyName('no').Value :=SelNo;
      Open;  //显示主库数据
      FindMasterStringGrid.ColCount := FieldCount;
      For ShowI:=0 to FieldCount-1 do
      begin
        FindMasterStringGrid.Cells[ShowI,0] := Fields.Fields[ShowI].FieldName;
        FindMasterStringGrid.Cells[ShowI,1] := Fields.Fields[ShowI].AsString;
      end;
      Close;
    end;

    with MDataForm.FindClientADOQuery do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select * from ClientData where 清单编号=:no');
      Parameters.ParambyName('no').Value :=SelNo;
      Open;  //显示主库数据
    end;
  except
    MainForm.PshowMessage('系统错误','查询并显示数据库内容时失败！',0);
  end;

end;

procedure TFindForm.DelSpeedButtonClick(Sender: TObject);
var DelNO :string;
    DelList :integer;
begin
  //删除选定的清单
  DelNo := FindMasterStringGrid.Cells[0,1];
  if DelNO<>'' then
  begin
    if MainForm.PshowMessage('提示','是否确定在删除编号为“'+DelNo+'”数据清单？',1) then
    begin
      try
        MDataForm.DataADOConnection.BeginTrans;
        with MDataForm.FreeFind_ADOQuery do
        begin
          Close;
          SQL.Clear;
          SQL.Add('Delete from MasterData where 清单编号=:OrderNo');
          Parameters.ParamByName('OrderNo').Value := DelNo;
          ExecSQL;
        end;
        with MDataForm.FreeADOQuery do
        begin
          Close;
          SQL.Clear;
          SQL.Add('Delete from ClientData where 清单编号=:OrderNo');
          Parameters.ParamByName('OrderNo').Value := DelNo;
          ExecSQL;
        end;
        MDataForm.DataADOConnection.CommitTrans;
        //清理界面
        for DelList:=0 to FindDataListBox.Items.Count-1 do
        begin
          if FindDataListBox.Items.Strings[DelList]= DelNo then
          begin
             FindDataListBox.items.Delete(DelList);
             Break;
          end;
        end;
        MDataForm.FindClientADOQuery.Close;
        for DelList :=0 to FindMasterStringGrid.ColCount-1 do
          FindMasterStringGrid.Cells[DelList,1] :='';
      except
        MainForm.PshowMessage('系统错误','删除选定的清单失败！',0);
        MDataForm.DataADOConnection.RollbackTrans;
      end;
    end;
  end;
end;

procedure TFindForm.FindListDBGridDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
  if (MDataForm.FindClientADOQuery.RecNo mod 2) = 1 then
  begin
     with FindListDBGrid do
     begin
        canvas.brush.color:= $00F2D5D0;
        DefaultDrawColumnCell(Rect, DataCol,Column,State);
     end;
  end;
end;

procedure TFindForm.DateCheckBoxClick(Sender: TObject);
begin
  EFindDateTime.Date := Date;
end;

procedure TFindForm.ModiSpeedButtonClick(Sender: TObject);
var SaveData :integer;
begin
  if FindMasterStringGrid.Cells[0,1]<>'' then
  begin
    if ModiSpeedButton.Caption ='修改数据(Ctrl+M)' then
    begin
      if MainForm.PshowMessage('提示','是否要进入数据修改状态？',1) then
      begin
        FindMasterStringGrid.Options := FindMasterStringGrid.Options +[goEditing]
                                                                     -[goRowSelect];
        ModiSpeedButton.Caption :='保存数据(Ctrl+S)';
        FindListDBGrid.FixedColor := clRed;
        FindDataListBox.Enabled := false;
        FindMasterStringGrid.FixedCols :=2;
        FindMasterStringGrid.SetFocus;
        FindListDBGrid.ReadOnly := false;
        FindListDBGrid.Options := FindListDBGrid.Options+[dgEditing]-[dgRowSelect];
        FindListDBGrid.Columns.Items[1].ReadOnly := true;
        PageControl1.ActivePageIndex :=0;
      end;
    end else
    begin
      if MainForm.PshowMessage('提示','确定要保存修改的数据吗？确定保存，取消不保存！',1) then
      begin  //保存修改数据
        try
          with MDataForm.InputMasterADOQuery do
          begin
            Close;
            SQL.Clear;
            SQL.Add('update masterdata set 客户名称=:ClientName, 客户品名=:ClientBreed, '+
                    '品种名称=:BreedName, 色号=:ColorNo, 幅宽=:Width, 成份=:cf, '+
                    '缸号=:gh, 合同编号=:htbh, 东越自编=:dyzb '+
                    'where 清单编号=:OrderNo');
            Parameters.ParamByName('OrderNo').Value := FindMasterStringGrid.Cells[0,1];
            Parameters.ParamByName('ClientName').Value := FindMasterStringGrid.Cells[2,1];
            Parameters.ParamByName('ClientBreed').Value := FindMasterStringGrid.Cells[3,1];
            Parameters.ParamByName('BreedName').Value := FindMasterStringGrid.Cells[4,1];
            Parameters.ParamByName('ColorNo').Value := FindMasterStringGrid.Cells[5,1];
            Parameters.ParamByName('Width').Value := FindMasterStringGrid.Cells[6,1];
            Parameters.ParamByName('cf').Value := FindMasterStringGrid.Cells[7,1];
            Parameters.ParamByName('gh').Value := FindMasterStringGrid.Cells[8,1];
            Parameters.ParamByName('htbh').Value := FindMasterStringGrid.Cells[9,1];
            Parameters.ParamByName('dyzb').Value := FindMasterStringGrid.Cells[10,1];
            ExecSQL;
          end;
        except
          MainForm.PshowMessage('系统错误','保存修改数据时失败！',0);
        end;
      end else
        FindDataListBoxClick(nil);//取消修改

      FindMasterStringGrid.Options := FindMasterStringGrid.Options -[goEditing]
                                                                   +[goRowSelect];
      ModiSpeedButton.Caption :='修改数据(Ctrl+M)';
      FindListDBGrid.FixedColor := clBtnFace;
      FindDataListBox.Enabled := true;
      FindMasterStringGrid.FixedCols :=0;
      FindListDBGrid.ReadOnly := true;
      FindListDBGrid.Options := FindListDBGrid.Options-[dgEditing]+[dgRowSelect];
      FindListDBGrid.Columns.Items[1].ReadOnly := false;
    end;
  end;

end;

procedure TFindForm.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if (ssCtrl in Shift) and (Key=word('F')) then FindSpeedButtonClick(nil);
end;

procedure TFindForm.FindListDBGridKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key=VK_Return then Key:=VK_Tab;
end;

procedure TFindForm.FormCreate(Sender: TObject);
begin
  PageControl1.ActivePageIndex :=0;
end;

procedure TFindForm.SpeedButton7Click(Sender: TObject);
var FindOK :boolean;
begin
  //打开部分数据
  FindOK := true;
  if (FindSmallEdit.Text='') or (FindMaxEdit.text='') then
  begin
    FindOK := false;
    MainForm.PshowMessage('提示','请指定编号范围！',0);
  end;
  if FindOK then
  begin
    try
      With MDataForm.FindClientADOQuery do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select * from ClientData where 清单编号=:no and 编号>=:sno and 编号<=:mno');
        Parameters.ParambyName('no').Value :=FindMasterStringGrid.Cells[0,1];
        Parameters.ParambyName('sno').Value := StrToInt(FindSmallEdit.Text);
        Parameters.ParambyName('mno').Value := StrToInt(FindMaxEdit.text);
        Prepared;
        Open;
      end;
    except
      MainForm.PshowMessage('系统错误','打开部分数据库时失败！请检查编号输入是否正确！',0);
    end;
  end;
end;

procedure TFindForm.PageControl1Change(Sender: TObject);
begin
  if PageControl1.ActivePageIndex =1 then FindSmallEdit.SetFocus;
end;

procedure TFindForm.FindMoreDBGridDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
  if (MDataForm.FindClientADOQuery.RecNo mod 2) = 1 then
  begin
     with FindMoreDBGrid do
     begin
        canvas.brush.color:= $00F2D5D0;
        DefaultDrawColumnCell(Rect, DataCol,Column,State);
     end;
  end;
end;

end.