untoutstock.pas

数据库实习个人作品

unit untOutStock;

interface

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

type
  TfrmOutStock = class(TForm)
    qryOrdersGrd: TADOQuery;
    qryOrderItemGrd: TADOQuery;
    uspTmp: TADOStoredProc;
    souOrderItemGrd: TDataSource;
    souOrderGrd: TDataSource;
    gupOrders: TGroupBox;
    grdOrders: TDBGrid;
    qryOrdersGrdOrderID: TStringField;
    qryOrdersGrdCustID: TStringField;
    qryOrdersGrdCustName: TStringField;
    qryOrdersGrdZip: TStringField;
    qryOrdersGrdAddress: TStringField;
    qryOrdersGrdState: TSmallintField;
    gupOrderItem: TGroupBox;
    grdOrderItem: TDBGrid;
    qryOrderItemGrdGoodsCode: TStringField;
    qryOrderItemGrdGoodsName: TStringField;
    qryOrderItemGrdUnit: TStringField;
    qryOrderItemGrdQuantity: TBCDField;
    btnOK: TBitBtn;
    btnShowPurchase: TButton;
    qryOrderItemGrdPrice: TBCDField;
    procedure souOrdersGrdDataChange(Sender: TObject; Field: TField);
    procedure btnShowPurchaseClick(Sender: TObject);
  private
    { Private declarations }
  public
    function Execute: Boolean;
    { Public declarations }
  end;

var
  frmOutStock: TfrmOutStock;

implementation

uses untGoodsOrderDat,untPurchase;

{$R *.dfm}

function TfrmOutStock.Execute: Boolean;
const
  cSqlOrderItem1 = 'SELECT A.GoodsCode,A.GoodsName,A.Unit,A.Price,B.Quantity ';
  cSqlOrderItem2 = 'FROM Goods A,OrderItem B ';
  cSqlOrderItem3 = 'WHERE A.GoodsCode=B.GoodsCode AND B.OrderID=''%s'' ORDER BY ItemNo';
var
  sOrderID: string;
begin
  //  下面对帐上的余额足够的顾客做发货处理：仓库有足够的货就发货，否则增加采购信息
  //  这里使用了Add的方法改变SQL语句的赋值，与以前的方式做一下比较
  //  只对状态为0（未处理）的定单处理
  qryOrdersGrd.SQL.Clear;
  qryOrdersGrd.SQL.Add('SELECT A.OrderID,A.CustID,B.CustName,B.Zip,B.Address,A.State ');
  qryOrdersGrd.SQL.Add('FROM Orders A,Customer B ');
  qryOrdersGrd.SQL.Add('WHERE A.CustID=B.CustID AND A.State=0 AND A.Amount<=B.AmountBal');
  qryOrdersGrd.Open;

  qryOrdersGrd.DisableControls; //  该语句的功能是：断开显示界面与数据库的联系
  qryOrdersGrd.First;           //  也就是说，对数据表的操作不会影响显示界面的改变，如指针移动
  while not qryOrdersGrd.Eof do
  begin
    sOrderID := Trim(qryOrdersGrd.FieldByName('OrderID').AsString);
    //  调用存储过程：根据定单号检验库存中是否有足够的商品
    //  若有一种不够，不发货，增加采购信息
    uspTmp.Parameters.Clear;
    uspTmp.ProcedureName := 'usp_CheckStock';
    uspTmp.Parameters.CreateParameter('OrderID',ftString,pdInput,11,sOrderID);
    uspTmp.Parameters.CreateParameter('ReportDate',ftString,pdInput,17,FormatDateTime('YYYYMMDD hh:mm:ss',now));
    uspTmp.Parameters.CreateParameter('Result',ftSmallInt,pdOutput,0,0);
    uspTmp.ExecProc;
    //  如果该订单没有足够的商品，不发货，检测下一个订单
    if uspTmp.Parameters.ParamByName('Result').Value = 0 then
    begin
      qryOrdersGrd.Next;
      Continue;   //  Continue与C语言中的Continue的含义相同
    end;

    //  调用存储过程：根据定单号出库
    uspTmp.Parameters.Clear;
    uspTmp.ProcedureName := 'usp_OutStock';
    uspTmp.Parameters.CreateParameter('OrderID',ftString,pdInput,11,sOrderID);
    uspTmp.Parameters.CreateParameter('DateOutStock',ftString,pdInput,17,FormatDateTime('YYYYMMDD hh:mm:ss',now));
    uspTmp.Parameters.CreateParameter('Result',ftSmallInt,pdOutput,0,0);
    uspTmp.ExecProc;

    //  调用存储过程：根据定单号检验库存中是否有足够的商品
    //  若有一种不够，增加采购信息
    uspTmp.Parameters.Clear;
    uspTmp.ProcedureName := 'usp_CheckStock';
    uspTmp.Parameters.CreateParameter('OrderID',ftString,pdInput,11,sOrderID);
    uspTmp.Parameters.CreateParameter('ReportDate',ftString,pdInput,17,FormatDateTime('YYYYMMDD hh:mm:ss',now));
    uspTmp.Parameters.CreateParameter('Result',ftSmallInt,pdOutput,0,0);
    uspTmp.ExecProc;

    //  下一个订单
    qryOrdersGrd.Next;
  end;
  qryOrdersGrd.EnableControls;  //  这条语句，是与DisableControls语句相对应的
  qryOrdersGrd.Refresh; //  执行该语句的目的是：用数据表中的最新数据刷新显示界面

  //  将待出货订单的当前记录定位在第一条记录
  //  订单明细中显示第一个订单的明细
  qryOrdersGrd.First;
  sOrderID := Trim(qryOrdersGrd.FieldByName('OrderID').AsString);

  qryOrderItemGrd.SQL.Text := cSqlOrderItem1 + cSqlOrderItem2 + Format(cSqlOrderItem3,[sOrderID]);
  qryOrderItemGrd.LockType := ltBatchOptimistic;
  qryOrderItemGrd.CursorType := ctKeyset;
  qryOrderItemGrd.ExecSQL;
  // qryOrderItemGrd.Open;  // 为什么这里用Open总是报错？
  // qryOrderItemGrd.Close;

  //  显示窗体，并返回函数结果
  Result := ShowModal = mrOK;
end;

procedure TfrmOutStock.souOrdersGrdDataChange(Sender: TObject; Field: TField);
const
  cSqlOrderItem1 = 'SELECT A.GoodsCode,A.GoodsName,A.Unit,A.Price,B.Quantity ';
  cSqlOrderItem2 = 'FROM Goods A,OrderItem B ';
  cSqlOrderItem3 = 'WHERE A.GoodsCode=B.GoodsCode AND B.OrderID=''%s'' ORDER BY ItemNo';
var
  sOrderID: string;
begin
  sOrderID := Trim(qryOrdersGrd.FieldByName('OrderID').AsString);

  qryOrderItemGrd.SQL.Text := cSqlOrderItem1 + cSqlOrderItem2 + Format(cSqlOrderItem3,[sOrderID]);
  qryOrderItemGrd.LockType := ltBatchOptimistic;
  qryOrderItemGrd.CursorType := ctKeyset;
  qryOrderItemGrd.Open;
end;

procedure TfrmOutStock.btnShowPurchaseClick(Sender: TObject);
var
  frmPurchase : TfrmPurchase;
begin
  frmPurchase := TfrmPurchase.Create(Application);
  try
    frmPurchase.Execute;
  finally
    frmPurchase.Free;
  end;
end;

end.