untcustorder.~pas

数据库实习个人作品

unit untCustOrder;

interface

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

type
  TfrmCustOrder = class(TForm)
    gupCustInfo: TGroupBox;
    bsBottomLine: TBevel;
    Bevel2: TBevel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    edtOrderID: TEdit;
    edtCustID: TEdit;
    edtcode: TEdit;
    edtCustName: TEdit;
    edtTel: TEdit;
    edtZip: TEdit;
    edtAddress: TEdit;
    rdoSexMale: TRadioButton;
    rdoSexFemale: TRadioButton;
    gupOrderItem: TGroupBox;
    qryOrderItemGrd: TADOQuery;
    uspTmp: TADOStoredProc;
    souOrderItemGrd: TDataSource;
    grdOrderItem: TDBGrid;
    btnAdd: TButton;
    btnDel: TButton;
    btnOK: TBitBtn;
    btnCancel: TBitBtn;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    procedure edtChange(Sender: TObject);
    procedure edtCustIDCodeExit(Sender: TObject);
    procedure grdOrderItemColExit(Sender: TObject);
    procedure btnAddClick(Sender: TObject);
    procedure btnDelClick(Sender: TObject);
    procedure btnOKClick(Sender: TObject);
  private
    IsOldCustomer: Boolean; //记录当前是否是老顾客

    function CheckAllRecords: Boolean;
    function SaveAllRecords: Boolean;
  public
    function Execute: Boolean;
  end;

var
  frmCustOrder: TfrmCustOrder;

implementation

uses untGoodsOrderDat;

{$R *.dfm}

function TfrmCustOrder.Execute: Boolean;
const
  cSqlOrderItem = 'SELECT A.GoodsCode,A.GoodsName,A.Unit,A.Price,B.Quantity FROM Goods A,OrderItem B WHERE 1=0';
var
  sOrderID: string;
begin
  // 设置数据库组件属性，设置数据表以批方式存取
  qryOrderItemGrd.SQL.Text := cSqlOrderItem;
  qryOrderItemGrd.LockType := ltBatchOptimistic;
  qryOrderItemGrd.CursorType := ctKeyset;
  qryOrderItemGrd.Open;

  // 调用存储过程，得到新订单号
  uspTmp.Parameters.Clear;
  uspTmp.ProcedureName := 'usp_GetNewOrderID'; // 存储过程usp_GetNewOrderID
  uspTmp.Parameters.CreateParameter('DateStr',ftString,pdInput,8,FormatDateTime('YYYYMMDD',now));//now获取当前时间
  uspTmp.Parameters.CreateParameter('OrderID',ftString,pdOutput,11,'');
  uspTmp.ExecProc;
  sOrderID := uspTmp.Parameters.ParamByName('OrderID').Value;
  // 初始化窗体显示的内容
  edtOrderID.Text := sOrderID;
  edtCustID.Text := '';
  edtCode.Text := '';
  edtCustName.Text := '';
  edtTel.Text := '';
  edtZip.Text := '';
  edtAddress.Text := '';
  rdoSexMale.Checked := True;
  rdoSexFemale.Checked := False;
  // 显示窗体，并返回函数结果
  Result := ShowModal = mrOK;
end;

function TfrmCustOrder.CheckAllRecords: Boolean;
begin
  Result := True;

  if Trim(edtCode.Text) = '' then
  begin
    Result := False;
    Exit;
  end;

  if Trim(edtCustName.Text) = '' then
  begin
    Result := False;
    Exit;
  end;

  if Trim(edtTel.Text) = '' then
  begin
    Result := False;
    Exit;
  end;

  if Trim(edtZip.Text) = '' then
  begin
    Result := False;
    Exit;
  end;

  if Trim(edtAddress.Text) = '' then
  begin
    Result := False;
    Exit;
  end;
end;

function TfrmCustOrder.SaveAllRecords: Boolean;
var
  dAmount,dPrice,dQuantity: Currency;
begin
  Result := False;

  with datGoodsOrder do
  begin
    // 开始事务提交
    conGoodsOrder.BeginTrans;

    try
      // 打开顾客表
      if IsOldCustomer then
        qryCustomer.SQL.Text := 'SELECT * From Customer WHERE CustID=''' + Trim(edtCustID.Text) + ''''
      else
        qryCustomer.SQL.Text := 'SELECT * FROM Customer WHERE 1=0';
      qryCustomer.LockType := ltBatchOptimistic;
      qryCustomer.CursorType := ctKeyset;
      qryCustomer.Open; //.ExecSQL;

      // 打开订单表
      qryOrders.SQL.Text := 'SELECT * FROM Orders WHERE 1=0';
      qryOrders.LockType := ltBatchOptimistic;
      qryOrders.CursorType := ctKeyset;
      qryOrders.Open;  //.ExecSQL;

      //打开订单明细表
      qryOrderItem.SQL.Text := 'SELECT * FROM OrderItem WHERE 1=0';
      qryOrderItem.LockType := ltBatchOptimistic;
      qryOrderItem.CursorType := ctKeyset;
      qryOrderItem.Open;  //.ExecSQL;

      // 修改或增加顾客记录
      if IsOldCustomer then
        qryCustomer.Edit
      else
      begin
        qryCustomer.Append;
        qryCustomer.FieldByName('CustID').AsString := Trim(edtCustID.Text);
        qryCustomer.FieldByName('Code').AsString := Trim(edtCode.Text);
      end;

      qryCustomer.FieldByName('CustName').AsString := Trim(edtCustName.Text);
      qryCustomer.FieldByName('Sex').AsString := '';
      if rdoSexMale.Checked then
        qryCustomer.FieldByName('Sex').AsString := 'M'
      else if rdoSexFemale.Checked then
        qryCustomer.FieldByName('Sex').AsString := 'F';
      qryCustomer.FieldByName('Tel').AsString := Trim(edtTel.Text);
      qryCustomer.FieldByName('Zip').AsString := Trim(edtZip.Text);
      qryCustomer.FieldByName('Address').AsString := Trim(edtAddress.Text);
      qryCustomer.Post;

      // 增加订单明细记录
      dAmount := 0.0;
      qryOrderItemGrd.First;
      while not qryOrderItemGrd.Eof do
      begin
        dPrice := qryOrderItemGrd.FieldByName('Price').AsCurrency;
        dQuantity := qryOrderItemGrd.FieldByName('Quantity').AsCurrency;
        dAmount := dAmount + dPrice * dQuantity;

        qryOrderItem.Append;
        qryOrderItem.FieldByName('OrderID').AsString := Trim(edtOrderID.Text);
        qryOrderItem.FieldByName('ItemNo').AsInteger := qryOrderItemGrd.RecNo + 1;
        qryOrderItem.FieldByName('GoodsCode').AsString := qryOrderItemGrd.FieldByName('GoodsCode').AsString;
        qryOrderItem.FieldByName('Quantity').AsCurrency := qryOrderItemGrd.FieldByName('Quantity').AsCurrency;
        qryOrderItem.Post;

        qryOrderItemGrd.Next;
      end;

      // 增加订单记录
      qryOrders.Append;
      qryOrders.FieldByName('OrderID').AsString := Trim(edtOrderID.Text);
      qryOrders.FieldByName('CustID').AsString := Trim(edtCustID.Text);
      qryOrders.FieldByName('Amount').AsCurrency := dAmount;
      qryOrders.Post;

      // 批更新数据表
      qryCustomer.UpdateBatch();
      qryOrders.UpdateBatch();
      qryOrderItem.UpdateBatch();
      // 事务提交
      conGoodsOrder.CommitTrans;
      Result := True;
    except
      // 回滚事务
      conGoodsOrder.RollbackTrans;
      MessageDlg('保存数据失败！',mtError,[mbOK],0);
      Close;
    end;
  end;
end;

procedure TfrmCustOrder.edtChange(Sender: TObject);
begin
  if CheckAllRecords then
  begin
    grdOrderItem.Enabled := True;
    btnAdd.Enabled := True;
    btnDel.Enabled := True;
  end
  else
  begin
    grdOrderItem.Enabled := False;
    btnAdd.Enabled := False;
    btnDel.Enabled := False;
  end;
end;

procedure TfrmCustOrder.edtCustIDCodeExit(Sender: TObject);
const
  cSqlCustom = 'SELECT * FROM Customer WHERE RTRIM(CustID)=''%s'' AND RTRIM(Code)=''%s''';
var
  sCustID,sCode: string;
begin
  sCustID := Trim(edtCustID.Text);  // Trim()是什么意思？  -- 滤掉字符串前面的空格
  sCode := Trim(edtCode.Text);      // 为什么要使用Trim()函数？
  // 下面程序段的功能是什么意思？
  if (sCustID <> '') and (sCode <> '') then
  begin
    with datGoodsOrder do
    begin
      qryCustomer.SQL.Text := Format(cSqlCustom,[sCustID,sCode]);
      qryCustomer.Open;
      // 如果是老顾客（为什么这样可以判断是老顾客？） -- qryCustomer查询结果不为空
      if not qryCustomer.IsEmpty() then
      begin
        IsOldCustomer := True;
        edtCustName.Text := qryCustomer.FieldByName('CustName').AsString;
        rdoSexMale.Checked := qryCustomer.FieldByName('Sex').AsString = 'M';  // 得到字符串与'M'比较的结果(true或false)
        rdoSexFemale.Checked := qryCustomer.FieldByName('Sex').AsString = 'F';
        edtTel.Text := qryCustomer.FieldByName('Tel').AsString;
        edtZip.Text := qryCustomer.FieldByName('Zip').AsString;
        edtAddress.Text := qryCustomer.FieldByName('Address').AsString;
      end
      else  // 否则是新顾客
      begin
        IsOldCustomer := False;
        ShowMessage('顾客号与身份证号不相符，该顾客是新顾客！');
        // 调用存储过程，得到新顾客号
        uspTmp.Parameters.Clear;
        uspTmp.ProcedureName := 'usp_GetNewCustID';
        uspTmp.Parameters.CreateParameter('DateStr',ftString,pdInput,8,FormatDateTime('YYYYMMDD',now));
        uspTmp.Parameters.CreateParameter('CustID',ftString,pdOutput,11,'');
        uspTmp.ExecProc;
        sCustID := uspTmp.Parameters.ParamByName('CustID').Value;
        edtCustID.Text := sCustID;
      end;
    end;
  end  
  else if (sCustID = '') and (sCode <> '') then
  begin // 如果订单号为空，表明是新顾客
    // 调用存储过程，得到新顾客号
    uspTmp.Parameters.Clear;
    uspTmp.ProcedureName := 'usp_GetNewCustID';
    uspTmp.Parameters.CreateParameter('DateStr',ftString,pdInput,8,FormatDateTime('YYYYMMDD',now));
    uspTmp.Parameters.CreateParameter('CustID',ftString,pdOutput,11,'');
    uspTmp.ExecProc;
    sCustID := uspTmp.Parameters.ParamByName('CustID').Value;
    edtCustID.Text := sCustID;
  end
  else if (sCustID <> '') and (sCode = '') then
  begin
    edtCode.SetFocus;
  end;
end;

procedure TfrmCustOrder.grdOrderItemColExit(Sender: TObject);
var
  sGoodsCode: string;
begin
  if grdOrderItem.SelectedIndex = 0 then
  begin
    sGoodsCode := qryOrderItemGrd.FieldByName('GoodsCode').AsString;
    // 调用存储过程，得到商品的名称、单位和单价
    uspTmp.Parameters.Clear;
    uspTmp.ProcedureName := 'usp_GetGoodsInfo';
    uspTmp.Parameters.CreateParameter('GoodsCode',ftString,pdInput,10,sGoodsCode);
    uspTmp.Parameters.CreateParameter('GoodsName',ftString,pdOutput,20,'');
    uspTmp.Parameters.CreateParameter('Find',ftSmallInt,pdOutput,0,0);
    uspTmp.Parameters.CreateParameter('Unit',ftString,pdOutput,20,'');
    uspTmp.Parameters.CreateParameter('Price',ftCurrency,pdOutput,0,0);
    uspTmp.Parameters.CreateParameter('SafeStock',ftCurrency,pdOutput,0,0);
    uspTmp.Parameters.CreateParameter('QtyBuyMin',ftCurrency,pdOutput,0,0);
    uspTmp.ExecProc;

    // 输出当前商品代码的商品名称、单位和单价
    qryOrderItemGrd.Edit;
    // 如果返回的几个值可能为空，数据转换时就会错误，为了避免中断程序执行，采用下面方法
    if uspTmp.Parameters.ParamByName('Find').Value = 1 then
    begin
      qryOrderItemGrd.FieldByName('GoodsName').AsString := uspTmp.Parameters.ParamByName('GoodsName').Value;
      qryOrderItemGrd.FieldByName('Unit').AsString := uspTmp.Parameters.ParamByName('Unit').Value;
      qryOrderItemGrd.FieldByName('Price').AsCurrency := uspTmp.Parameters.ParamByName('Price').Value;
    end;
    qryOrderItemGrd.Post;
    grdOrderItem.SelectedIndex := 4;  // 是什么意思？
  end;
end;

procedure TfrmCustOrder.btnAddClick(Sender: TObject);
begin
  qryOrderItemGrd.Append;
end;

procedure TfrmCustOrder.btnDelClick(Sender: TObject);
begin
  qryOrderItemGrd.Delete;
end;

procedure TfrmCustOrder.btnOKClick(Sender: TObject);
begin
  // 如果处于编辑状态，退出编辑状态
  if qryOrderItemGrd.State = dsEdit then
    qryOrderItemGrd.Post;
  // 如果订单明细不为空，才认为该订单是有效订单
  if not qryOrderItemGrd.IsEmpty then
    SaveAllRecords;
end;

end.