kcjgtz.pas

仓库管理系统的SQL源代码

unit kcjgtz;

interface

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

type
  TForm34 = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    Label1: TLabel;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    SpeedButton1: TSpeedButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Panel3: TPanel;
    SpinEdit1: TSpinEdit;
    Label3: TLabel;
    Label4: TLabel;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    Label5: TLabel;
    Edit1: TEdit;
    Panel4: TPanel;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    procedure Edit1KeyPress(Sender: TObject; var Key: Char);
    procedure ComboBox1KeyPress(Sender: TObject; var Key: Char);
    procedure ComboBox1Change(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure Edit1Exit(Sender: TObject);
    procedure BitBtn1MouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure BitBtn1MouseUp(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure BitBtn2Click(Sender: TObject);
    procedure ComboBox1KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form34: TForm34;

implementation
  uses DataModal;
{$R *.dfm}

procedure TForm34.Edit1KeyPress(Sender: TObject; var Key: Char);
var
  m: Boolean;
begin
  m := (Key <#8)or(Key>#8)and(Key <#46)or(key>#46)and(Key<#48)or(Key >#57);
  if m then
    Key := #0;
    
end;

procedure TForm34.ComboBox1KeyPress(Sender: TObject; var Key: Char);
begin
  Key := #0;
end;

procedure TForm34.ComboBox1Change(Sender: TObject);
begin
  if Trim(ComboBox1.Text)<>'' then
  begin
    with Data.ADOQuery1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select distinct');
      SQL.Add(ComboBox1.Text);
      SQL.Add('From 库存表' );
      Open;
    end;
    if Data.ADOQuery1.RecordCount>0 then
    begin
      ComboBox2.Clear;
      while Not Data.ADOQuery1.Eof do
      begin
        ComboBox2.Items.Add(Data.ADOQuery1.Fields[0].Value);
        Data.ADOQuery1.Next;
      end;
      ComboBox2.ItemIndex := 0;
    end;
  end;
end;

procedure TForm34.SpeedButton1Click(Sender: TObject);
begin
  if (Trim(ComboBox1.Text)<>'')and(Trim(ComboBox2.Text)<>'')then
  begin
    With Data.ADOQuery2 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select * from 商品基础信息表 where ');
      SQL.Add(ComboBox1.Text);
      SQL.Add('=:a' );
      Parameters.ParamByName('a').Value := Trim(ComboBox2.Text);
      Open;
    end;
    if Data.ADOQuery2.RecordCount>0 then
    begin
      DataSource1.DataSet := Data.ADOQuery2;
      BitBtn1.Enabled := True;
    end
    else
    begin
      BitBtn1.Enabled := False;
      DataSource1.DataSet := Nil;
      Application.MessageBox('没有该商品的相关信息。','提示',64);
    end;
  end;
end;

procedure TForm34.BitBtn1Click(Sender: TObject);
var
  jg,ee: Real;
  sum: Integer;//记录商品库存数量
begin
  if (RadioButton1.Checked = True)and (StrToInt(SpinEdit1.Text)= 0) then
  begin
    Application.MessageBox('价格百分比不能为零.','提示',64);
    SpinEdit1.SetFocus;
    Exit;
  end;
  if RadioButton2.Checked = True then
    Edit1.OnExit(Sender);
  if Application.MessageBox(Pchar('确实要修改商品编号为' + Trim(Data.ADOQuery2.FieldByName('商品编号').Value) + '的商品价格吗？'),'提示',MB_YesNo)= ID_Yes then
  begin
    if RadioButton1.Checked = True then
    begin
      ee := Data.ADOQuery2.FieldByName('进价').Value * (StrToFloat(SpinEdit1.Text)/100);
      jg :=  StrToFloat(Format('%8.2f',[ee]));
    end
    else
      jg := StrToFloat(Edit1.Text);
    Data.ADOConnection1.BeginTrans;
    Try
      Data.ADOQuery2.Edit;
      Data.ADOQuery2.FieldByName('进价').Value := jg;
      Data.ADOQuery2.Post;
      with Data.ADOQuery1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select 库存数量 from 库存表 where 商品编号 = :a');
        Parameters.ParamByName('a').Value := Trim(Data.ADOQuery2.FieldByName('商品编号').Value);
        Open;
      end;
      Sum := Data.ADOQuery1.FieldByName('库存数量').Value;
      with Data.ADOQuery1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('update 库存表 set 库存金额 = :a where 商品编号 = :b');
        Parameters.ParamByName('a').Value := jg*Sum;
        Parameters.ParamByName('b').Value := Trim(Data.ADOQuery2.FieldByName('商品编号').Value);
        ExecSQL;
      end;
      Data.ADOConnection1.CommitTrans;
      Application.MessageBox('库存金额修改成功。','提示',64);
      BitBtn2.OnClick(Sender);
    Except
      Data.ADOConnection1.RollbackTrans;
      Application.MessageBox('系统出错。','提示',64);
    end;
  end;
end;

procedure TForm34.Edit1Exit(Sender: TObject);
begin
  if Trim(Edit1.Text)='' then
  begin
    Application.MessageBox('商品价格不能为空。','提示',64);
    Edit1.SetFocus;
  end;
  if StrToFloat(Edit1.Text)<=0 then
  begin
    Application.MessageBox('商品价格不能小于等于零。','提示',64);
    Exit;
  end;
  Try
    StrToFloat(Edit1.Text);
  Except
    Application.MessageBox('请输入合法字符','提示',64);
    Edit1.SetFocus;
    Exit;
  end;
end;

procedure TForm34.BitBtn1MouseDown(Sender: TObject; Button: TMouseButton;
  Shift: TShiftState; X, Y: Integer);
begin
  TBitBtn(Sender).Font.Color := clRed;
end;

procedure TForm34.BitBtn1MouseUp(Sender: TObject; Button: TMouseButton;
  Shift: TShiftState; X, Y: Integer);
begin
  TBitBtn(Sender).Font.Color := clBlue;
end;

procedure TForm34.BitBtn2Click(Sender: TObject);
begin
  RadioButton1.Checked := True;
  SpinEdit1.Value := 100;
  Edit1.Text := '1.0';
  BitBtn1.Enabled := False;
end;

procedure TForm34.ComboBox1KeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key = VK_ReTurn then
    ComboBox1.OnChange(Sender);
end;

end.