untcustorder.~pas

这是一个不错的数据库管理程序

                                               unit untCustOrder;

interface

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

type
  TfrmCustOrder = class(TForm)
    qryOrderItemGrd: TADOQuery;
    uspTmp: TADOStoredProc;
    souOrderItemGrd: TDataSource;
    gupCustInfo: TGroupBox;
    Bevel1: TBevel;
    Bevel2: TBevel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    edtOrderID: TEdit;
    edtCustID: TEdit;
    edtCustName: TEdit;
    edtTel: TEdit;
    edtZip: TEdit;
    edtAddress: TEdit;
    rdoSexMale: TRadioButton;
    rdoSexFemale: TRadioButton;
    gupOrderItem: TGroupBox;
    grdOrderItem: TDBGrid;
    btnAdd: TButton;
    btnDel: TButton;
    btnOK: TBitBtn;
    btnCancel: TBitBtn;
    edtCode: TEdit;

    procedure edtCustIDCodeExit(Sender: TObject);
    procedure grdOrderItemColExit(Sender: TObject);
    procedure btnOKClick(Sender: TObject);
    procedure btnAddClick(Sender: TObject);
    procedure btnDelClick(Sender: TObject);
    procedure edtChange(Sender: TObject);
  private
        IsOldCustomer:Boolean;
        function CheckAllRecords: Boolean;
        function SaveAllRecords: Boolean;
    { Private declarations }
  public
        function Execute:Boolean;
    { Public declarations }
  end;

var
  frmCustOrder: TfrmCustOrder;

implementation

uses untGoodsOrderDat;


function TfrmCustOrder.Execute:Boolean;
const
        cSqlOrderItem='SELECT A.GoodsCode,A.GoodsName,A.Unit,A.Price,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';
        uspTmp.Parameters.CreateParameter('DataStr',ftString,pdInput, 8, FormatDateTime('YYYYMMDD',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;
                        //打开订单表
                        qryCustomer.SQL.Text := 'SELECT * FROM Orders WHERE 1=0';
                        qryCustomer.LockType := ltBatchOptimistic;
                        qryCustomer.CursorType := ctKeyset;
                        qryCustomer.open;
                        //打开订单明细表
                        qryCustomer.SQL.Text := 'SELECT * FROM OrderItem WHERE 1=0';
                        qryCustomer.LockType := ltBatchOptimistic;
                        qryCustomer.CursorType := ctKeyset;
                        qryCustomer.open;

                        //修改或增加顾客记录
                        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.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       // with ... do是什么意思？
                        qryCustomer.SQL.Text := Format(cSqlCustom, [sCustID, sCode]);
                        qryCustomer.Open;
                        // 如果是老顾客（为什么这样可以判断是老顾客？）
                        if not qryCustomer.IsEmpty() then begin
                                IsOldCustomer := True;
                                edtCustName.Text := qryCustomer.FieldByName('CustName').AsString;
                                rdoSexMale.Checked := qryCustomer.FieldByName('Sex').AsString = 'M';
                                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, pdInput, 11, '');
                                uspTmp.ExecProc;
                                sCustID := uspTmp.Parameters.ParamByName('CustID').Value;
                                edtCustID.Text := sCustID;
                        end;
                end;
        end;
        // 如果订单号为空，表明是新顾客
        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.ParamVALUES['CustID'];
                edtCustID.Text := sCustID;
        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.btnOKClick(Sender: TObject);
begin
        // 如果处于编辑状态，退出编辑状态
        if qryOrderItemGrd.State = dsEdit then
                qryOrderItemGrd.Post;
        // 如果订单明细不为空，才认为该订单是有效订单
        if not qryOrderItemGrd.IsEmpty then
                SaveAllRecords;
end;


{$R *.DFM}



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

procedure TfrmCustOrder.btnDelClick(Sender: TObject);
begin
        qryOrderItemGrd.Delete;
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;

end.