uquery.~pas

在公司做的一个小的系统源代码

unit Uquery;

interface

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

type
  Tfrmquery = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    edtscqy: TEdit;
    Label2: TLabel;
    edtypmc: TEdit;
    Label3: TLabel;
    edtspm: TEdit;
    Label4: TLabel;
    edtwh: TEdit;
    Label6: TLabel;
    edtzblsj1: TEdit;
    Label7: TLabel;
    edtzblsj2: TEdit;
    Label8: TLabel;
    dtzbrq1: TDateTimePicker;
    Label9: TLabel;
    dtzbrq2: TDateTimePicker;
    Label5: TLabel;
    GroupBox1: TGroupBox;
    DBGrid1: TDBGrid;
    ADOQzbcgb: TADOQuery;
    DataSource1: TDataSource;
    ADOQuery1: TADOQuery;
    StatusBar1: TStatusBar;
    edtfwdw: TEdit;
    GroupBox2: TGroupBox;
    GroupBox3: TGroupBox;
    Sptnxiugai: TSpeedButton;
    GroupBox4: TGroupBox;
    sptndelete: TSpeedButton;
    GroupBox5: TGroupBox;
    Sptnbrowse: TSpeedButton;
    GroupBox6: TGroupBox;
    Sptnclose: TSpeedButton;
    sptnquery: TSpeedButton;
    procedure FormCreate(Sender: TObject);
    procedure SptnqueryClick(Sender: TObject);
    procedure SptnxiugaiClick(Sender: TObject);
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure sptndeleteClick(Sender: TObject);
    procedure SptncloseClick(Sender: TObject);
    procedure SptnbrowseClick(Sender: TObject);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure edtscqyKeyPress(Sender: TObject; var Key: Char);
    procedure edtypmcKeyPress(Sender: TObject; var Key: Char);
    procedure edtwhKeyPress(Sender: TObject; var Key: Char);
    procedure edtspmKeyPress(Sender: TObject; var Key: Char);
    procedure edtzblsj1KeyPress(Sender: TObject; var Key: Char);
    procedure edtzblsj2KeyPress(Sender: TObject; var Key: Char);
    procedure dtzbrq1KeyPress(Sender: TObject; var Key: Char);
    procedure FormShow(Sender: TObject);
    procedure FormResize(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
   
  private
    { Private declarations }
   mousedown:integer;
  str:string;
  public
    { Public declarations }
     a,b:integer;  //a 记录修改当前序号，如果相等则刷新颜色
  end;             //b 记录删除当前序号，如果相等则刷新颜色

var
  frmquery: Tfrmquery;

implementation

uses Udm, Ulgb,uxiugai, Uprint;

{$R *.dfm}

procedure Tfrmquery.FormCreate(Sender: TObject);
begin
mousedown:=0;
 edtfwdw.Text:=gjgmc+'物价局';
 edtfwdw.Enabled:=false;
 { adoqopen(adoquery1, 'select * from zzjjgb' ) ;
  cmbfwdw.Clear;
  cmbfwdw.Text:='所有地市';
  cmbfwdw.items.add('所有地市') ;
  with adoquery1 do
  begin
      first;
      while not eof do begin
        cmbfwdw.Items.Add(fieldbyname('jgmc').AsString );
        next;
      end;
     cmbfwdw.ItemIndex :=-1;
   end;   }
sptnxiugai.Enabled:=false;
sptndelete.Enabled:=false;

end;


procedure Tfrmquery.SptnqueryClick(Sender: TObject);
begin
   str:='select zbcgb.*,str(zblsj,10,2) as jg,zzjjgb.jgmc as jgmc from zbcgb,zzjjgb where (zbrq>= :ksrq and zbrq< =:jsrq) and (zzjjgb.jgbm=zbcgb.jgbm) and zbcgb.jgbm='+#39+gjgbm +#39;

  if trim(edtscqy.Text )<>'' then
    str:=str+' and scqy like '+#39+'%'+trim(edtscqy.Text )+'%'+#39;
  if trim(edtypmc.Text )<>'' then
    str:=str+' and ypmc like '+#39+'%'+trim(edtypmc.Text )+'%'+#39;
  if trim(edtwh.Text )<>'' then
    str:=str+' and wh like'+#39+'%'+trim(edtwh.Text )+'%'+#39;
  if trim(edtspm.Text )<>'' then
    str:=str+' and spm like'+#39+'%'+trim(edtspm.Text )+'%'+#39;
  if trim(edtzblsj2.Text) <> '' then
    str := str + ' and zblsj<= ' + trim(edtzblsj2.Text);
  if trim(edtzblsj1.Text) <> '' then
    str := str + ' and zblsj>= ' + trim(edtzblsj1.Text);
 // if trim(cmbfwdw.text)<>'所有地市' then
  //  str:=str+'and zzjjgb.jgmc='+#39+trim(cmbfwdw.Text)+#39;
with adoqzbcgb do
  begin
    close;
    sql.Clear;
    sql.Add(str);
     Parameters.ParamByName('ksrq').Value := datetostr(dtzbrq1.Date);
    Parameters.ParamByName('jsrq').Value := datetostr(dtzbrq2.Date);
     try
    open;
    except
      begin
        application.MessageBox(Gcntinfo,'系统提示',MB_OK+MB_ICONINFORMATION);
        abort;
      end;
    end;
  end;
 if adoqzbcgb.IsEmpty then
 begin
    application.MessageBox('没有符合条件的药品！',gsysname,MB_OK+MB_ICONINFORMATION);
    sptnxiugai.Enabled:=false;
    sptndelete.Enabled:=false;
    abort;
 end
 else
    begin
    if gpower ='1' then
    begin
    sptnxiugai.Enabled:=true;
    sptndelete.Enabled:=true;
    end;
end;
end;

procedure Tfrmquery.SptnxiugaiClick(Sender: TObject);
begin
   gquerybz:=true;
   application.CreateForm(tfrmxiugai,frmxiugai);
   a:=adoqzbcgb.RecNo;
   dbgrid1.Refresh;
 with  adoqzbcgb do
  begin
    frmxiugai.edtscqy.Text:=trim(fieldbyname('scqy').AsString) ;
    frmxiugai.edtypmc.Text:=trim(fieldbyname('ypmc').AsString);
    frmxiugai.edtspm.Text:=trim(fieldbyname('spm').AsString);
    frmxiugai.edtwh.Text:=trim(fieldbyname('wh').AsString);
    frmxiugai.edtfwdw.Text:=trim(fieldbyname('jgmc').AsString);
    frmxiugai.edtjx.Text:=trim(fieldbyname('jx').AsString);
    frmxiugai.edtgg.Text:=trim(fieldbyname('gg').AsString);
    frmxiugai.edtdw.Text:=trim(fieldbyname('dw').AsString);
    frmxiugai.edtzblsj.Text:=floattostr(fieldbyname('zblsj').asfloat);
    frmxiugai.dateTimePicker1.DateTime:=fieldbyname('zbrq').value;
    frmxiugai.edtzlcc.Text:=trim(fieldbyname('zlcc').AsString);
    frmxiugai.combobox1.Text:=trim(fieldbyname('glfs').AsString);
end;
 frmxiugai.ShowModal ;
 dbgrid1.Refresh;
end;

procedure Tfrmquery.DBGrid1DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
begin
  with adoqzbcgb do begin
  if  (adoqzbcgb.RecNo =a)then
     begin
       dbgrid1.Canvas.Brush.color:=clgreen;
     end;
     //////////////////////
     if ( adoqzbcgb.RecNo =b) then
     begin
      dbgrid1.Canvas.Brush.color:=clred;
     end;
     end;
  dbgrid1.DefaultDrawColumnCell(rect,datacol,column,state);
end;

end;

procedure Tfrmquery.sptndeleteClick(Sender: TObject);
begin
 b:=adoqzbcgb.RecNo;//当前操作记录号(序号)
 dbgrid1.Refresh;
 if application.messagebox(Pchar('确实要删除序号为  '+inttostr(datasource1.DataSet.Fields[0].asinteger)+#13
         +'药品名为：'+adoqzbcgb.fieldbyname('ypmc').asstring+#13+'  的药品吗？'), '系统提示',mb_okcancel+mb_iconexclamation)=idok then
  begin
  adoqopen(adoquery1,'select  *  from zbcgb where zbcgb.jgbm ='+#39+gjgbm+#39);
  adoquery1.SQL.Add('delete zbcgb where id='+inttostr(datasource1.DataSet.Fields[0].asinteger));
  adoquery1.ExecSQL;
   str:='select zbcgb.*,str(zblsj,10,2) as jg,zzjjgb.jgmc as jgmc from zbcgb,zzjjgb where (zbrq>= :ksrq and zbrq< =:jsrq) and (zzjjgb.jgbm=zbcgb.jgbm) and zbcgb.jgbm='+#39+gjgbm +#39;

  if trim(edtscqy.Text )<>'' then
    str:=str+' and scqy like '+#39+'%'+trim(edtscqy.Text )+'%'+#39;
  if trim(edtypmc.Text )<>'' then
    str:=str+' and ypmc like '+#39+'%'+trim(edtypmc.Text )+'%'+#39;
  if trim(edtwh.Text )<>'' then
    str:=str+' and wh like'+#39+'%'+trim(edtwh.Text )+'%'+#39;
  if trim(edtspm.Text )<>'' then
    str:=str+' and spm like'+#39+'%'+trim(edtspm.Text )+'%'+#39;
  if trim(edtzblsj2.Text) <> '' then
    str := str + ' and zblsj<= ' + trim(edtzblsj2.Text);
  if trim(edtzblsj1.Text) <> '' then
    str := str + ' and zblsj>= ' + trim(edtzblsj1.Text);
 // if trim(cmbfwdw.text)<>'所有地市' then
  //  str:=str+'and zzjjgb.jgmc='+#39+trim(cmbfwdw.Text)+#39;
with adoqzbcgb do
  begin
    close;
    sql.Clear;
    sql.Add(str);
     Parameters.ParamByName('ksrq').Value := datetostr(dtzbrq1.Date);
    Parameters.ParamByName('jsrq').Value := datetostr(dtzbrq2.Date);
     try
    open;
    except
      begin
        application.MessageBox( Gcntinfo,'系统提示',MB_OK+MB_ICONINFORMATION);
        abort;
      end;
    end;
  end;
   adoqzbcgb.MoveBy(b-1);
  b:=0;
  dbgrid1.Refresh;
  end
  else
 b:=0;
  if  DataSource1.DataSet.RecordCount=0 then
  begin
    sptndelete.Enabled:=false;
    sptnxiugai.Enabled:=false;
    b:=0;
  end;  

end;

procedure Tfrmquery.SptncloseClick(Sender: TObject);
begin
adoqzbcgb.Close;
adoquery1.Close;
close ;
end;

procedure Tfrmquery.SptnbrowseClick(Sender: TObject);
begin
application.CreateForm(tfrmprint,frmprint);
 frmprint.quickrep1.prepare;
 if frmprint.quickrep1.printer.pagecount>1 then
   Application.MessageBox('打印页数超过一页，请注意放纸！','招标采购药品价格系统',MB_ICONINFORMATION);
  frmprint.qrlpage.Caption :='共'+inttostr(frmprint.quickrep1.Printer.PageCount )+'页';
  frmprint.quickrep1.Refresh ;
 frmprint.QuickRep1.Preview;
 frmprint.QuickRep1.Free ;
 //frmprint.Free;
 end;

procedure Tfrmquery.DataSource1DataChange(Sender: TObject; Field: TField);
begin
statusbar1.Panels[0].Text:='当前药品：序号为'+datasource1.DataSet.Fields[0].AsString;
 statusbar1.Panels[1].Text:='药品总数：'+IntToStr(DataSource1.DataSet.RecordCount);
end;


procedure Tfrmquery.edtscqyKeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then
   begin
     key:=#0;
     edtypmc.SetFocus;
     edtypmc.SelectAll;
  end;
end;
procedure Tfrmquery.edtypmcKeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then
   begin
     key:=#0;
     edtwh.SetFocus;
     edtwh.SelectAll;
  end;
end;

procedure Tfrmquery.edtwhKeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then
   begin
     key:=#0;
     edtspm.SetFocus;
     edtspm.SelectAll;
  end;
end;

procedure Tfrmquery.edtspmKeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then
   begin
     key:=#0;
     edtzblsj1.SetFocus;
     edtzblsj1.SelectAll;

  end;
end;

procedure Tfrmquery.edtzblsj1KeyPress(Sender: TObject; var Key: Char);
begin
if(key>'0')and(key<'9')or(key='0')or(key='9')or(key=#13)or(key='.') or(key=#8)
    then  else
   abort;
  if key=#13 then
   begin
     key:=#0;
     edtzblsj2.SetFocus;
     edtzblsj2.SelectAll;
   end;
end;

procedure Tfrmquery.edtzblsj2KeyPress(Sender: TObject; var Key: Char);
begin
if(key>'0')and(key<'9')or(key='0')or(key='9')or(key=#13)or(key='.') or(key=#8)
    then  else
   abort;
  if key=#13 then
   begin
     key:=#0;
     dtzbrq1.SetFocus;
   end;
end;

procedure Tfrmquery.dtzbrq1KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then
   begin
     key:=#0;
     dtzbrq2.SetFocus;
  end;
end;

procedure Tfrmquery.FormShow(Sender: TObject);
begin
dtzbrq2.DateTime:=now;
end;

procedure Tfrmquery.FormResize(Sender: TObject);
begin
dbgrid1.Left:=0;
dbgrid1.top:=130;
dbgrid1.width:=frmquery.Width-18;
dbgrid1.Height:=frmquery.Height-190;

end;

procedure Tfrmquery.DBGrid1TitleClick(Column: TColumn);
begin
if adoqzbcgb.Active=false then
   abort;
 mousedown :=mousedown + 1;
  if mousedown mod 2 = 1 then
    ADOQzbcgb.Sort := column.FieldName
  else
   ADOQzbcgb.Sort := column.FieldName + '   ' + 'DESC';
 end;

end.