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.