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;
    souOrderGrd: TDataSource;
    souOrderItemGrd: TDataSource;
    gupOrderItem: TGroupBox;
    gupOrders: TGroupBox;
    grdOrders: TDBGrid;
    grdOrderItem: TDBGrid;
    qryOrderItemGrdGoodsCode: TStringField;
    qryOrderItemGrdGoodsName: TStringField;
    qryOrderItemGrdUnit: TStringField;
    qryOrderItemGrdQuantity: TBCDField;
    btnOK: TBitBtn;
    qryOrdersGrdOrderID: TStringField;
    qryOrdersGrdCustID: TStringField;
    qryOrdersGrdCustName: TStringField;
    qryOrdersGrdZip: TStringField;
    qryOrdersGrdAddress: TStringField;
    qryOrdersGrdState: TSmallintField;
    Button1: TButton;
    procedure PurChaseClick(Sender: TObject);
    procedure  souOrderGrdDataChange(Sender: TObject; Field: TField);
     Private 
    { Private declarations }
  public
  function Execute: Boolean;

    { Public declarations }
  end;

var
  frmOutStock: TfrmOutStock;

implementation
uses untPurChase;
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语句的赋值，与以前的方式做下比较
  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('Result', ftSmallInt, PdOutput, 0, 0);
    uspTmp.ExecProc;
    // 如果该订单没有足够的商品，不发货，检测下一个订单
    if uspTmp.Parameters.ParamByName('Result').Value = 0 then begin
      qryOrdersGrd.Next;
      Continue;
    end;

    //调用储存过程，根据订单号出库
    uspTmp.Parameters.Clear;
    uspTmp.ProcedureName := 'usp_OutStock';
    uspTmp.Parameters.CreateParameter('OrderID', ftString, pdInput, 11, sOrderID);
    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('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.Open;

  // 显示窗体，并反回结果
  result := ShowModal = mrOK;
end;
procedure TfrmOutStock.souOrderGrdDataChange(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.PurChaseClick(Sender: TObject);
var
 frmPurChase : TfrmPurChase;
begin
  frmPurChase := TfrmPurChase.create(application);
  try
   frmPurChase.Execute;
  finally
  	frmPurChase.Free;
  end;
end;





{$R *.dfm}

end.