unit STStockChange;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WSVoucherBrowse, DB, ActnList, Grids,WSEdit, DBGrids, QLDBGrid,
  ComCtrls, ExtCtrls, ToolWin,DateUtils, ADODB, StdCtrls, Buttons, Menus;

type
  TSTStockChangeFrom = class(TWSVoucherBrowseForm)
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton7: TToolButton;
    ToolButton8: TToolButton;
    ToolButton9: TToolButton;
    ToolButton11: TToolButton;
    ToolButton12: TToolButton;
    ToolButton13: TToolButton;
    adsStockChange: TADODataSet;
    Panel2: TPanel;
    Label1: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    Label2: TLabel;
    BitBtn1: TBitBtn;
    ADOQuery: TADOQuery;
    adsStockChangeDSDesigner: TStringField;
    adsStockChangeDSDesigner2: TStringField;
    adsStockChangeDSDesigner3: TStringField;
    adsStockChangeDSDesigner4: TBCDField;
    adsStockChangeDSDesigner5: TBCDField;
    adsStockChangeDSDesigner6: TBCDField;
    adsStockChangeDSDesigner7: TStringField;
    adsStockChangeDSDesigner8: TBCDField;
    adsStockChangeDSDesigner9: TBCDField;
    adsStockChangeDSDesigner10: TBCDField;
    adsStockChangeDSDesigner11: TBCDField;
    adsStockChangeDSDesigner12: TBCDField;
    adsStockChangeDSDesigner13: TBCDField;
    adsStockChangeDSDesigner14: TBCDField;
    adsStockChangeDSDesigner15: TBCDField;
    adsStockChangeDSDesigner16: TBCDField;
    Label3: TLabel;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure FormDeactivate(Sender: TObject);
    procedure DBGridCellClick(Column: TColumn);
    procedure DBGridDblClick(Sender: TObject);
    procedure UpdateDBGrid;
    procedure DBGridTitleClick(Column: TColumn);

  private
    { Private declarations }
  public
    { Public declarations }
  protected
    function CreateEditForm: TWSEditForm; override;
  end;

var
  STStockChangeFrom: TSTStockChangeFrom;

implementation

uses CommonDM ;

{$R *.dfm}

function TSTStockChangeFrom.CreateEditForm: TWSEditForm;
begin
//  Result := TSLEdClearBillAForm.Create(Self);
end;


procedure TSTStockChangeFrom.BitBtn1Click(Sender: TObject);
var year,month,day,year1,month1,day1 :word;
begin
  DecodeDate(DateTimePicker1.Date,year, month, day);
  DecodeDate(DateTimePicker2.Date,year1, month1, day1);
   ADOQuery.Close;
   ADOQuery.SQL.Text :=' TRUNCATE TABLE #TEMPSTOCK' ;
   ADOQuery.ExecSQL;
   ADOQuery.Close;
   ADOQuery.SQL.Text :=' INSERT INTO  #TEMPSTOCK ('
        +' warehouseID,GoodsID,GoalUnitID,FGoalQuantity,FAmount )'
        +' SELECT warehouseID,GoodsID,GoalUnitID,GoalQuantity,Amount '
        +'  FROM ('
        +' select b.date,b.warehouseID,GoodsID, GoalUnitID,  '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity*(-1) AS GoalQuantity,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount*(-1) as Amount from SLGoodsOutDetail a                                  '
        +' left outer join SLGoodsOutMaster b on b.ID=a.MasterID   ' //期初銷售出庫
        +' UNION ALL '
        +' select b.date,b.warehouseID,GoodsID,GoalUnitID,           '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantity,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount*(1) as Amount from PCGoodsIndetail a '
        +' left outer join PCGoodsInMaster b on b.ID=a.MasterID ' //期初采購入庫
        +' UNION ALL '
        +' select b.date,b.warehouseID,GoodsID, GoalUnitID,  '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity*(-1) AS GoalQuantity,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount*(-1) as Amount from YDGoodsOutDetail a   '
        +' left outer join YDGoodsOutMaster b on b.ID=a.MasterID   ' //期初生產出庫
        +' UNION ALL '
        +' select b.date,b.warehouseID,GoodsID,GoalUnitID,           '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantity,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as Amount from YDGoodsIndetail a   '
        +' left outer join YDGoodsInMaster b on b.ID=a.MasterID ' //期初生產入庫
        +' UNION ALL '
        +' select b.date,b.ClientID as warehouseID,GoodsID,GoalUnitID,           '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity*(-1) AS GoalQuantity,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount*(-1) as Amount '
        +' from STGoodsOutIndetail a                                          '
        +' left outer join STGoodsOutInMaster b on b.ID=a.MasterID ' //期初庫存調出
        +' UNION ALL '
        +' select b.date,b.warehouseID,GoodsID,GoalUnitID,           '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantity,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as Amount from STGoodsOutIndetail a  '
        +' left outer join STGoodsOutInMaster b on b.ID=a.MasterID ' //期初庫存調入

        +' UNION ALL '
        +' select b.date,b.ClientID as warehouseID,GoodsID,GoalUnitID,           '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity*(-1) AS GoalQuantity,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount*(-1) as Amount '
        +' from STGoodsCountOffDetail a                                          '
        +' left outer join STGoodsCountOffMaster b on b.ID=a.MasterID '
        +' where  b.BillMode<>'+Quotedstr('庫存盤盈')  //庫存盤點表 (盤虧\生產損耗倉庫)
        +' UNION ALL '
        +' select b.date,b.warehouseID,GoodsID,GoalUnitID,           '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantity,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as Amount from STGoodsCountOffDetail a  '
        +' left outer join STGoodsCountOffMaster b on b.ID=a.MasterID '
        +' where  b.BillMode='+Quotedstr('庫存盤盈')           //期初庫存調入
        +' ) AS bb'
        +' WHERE bb.DATE<='+Quotedstr(Datetostr(DateTimePicker1.Date)) ;
   ADOQuery.ExecSQL;
   ADOQuery.Close;
   ADOQuery.SQL.Text :=' INSERT INTO  #TEMPSTOCK ('
        +' date,BillMode,warehouseID,GoodsID,GoalUnitID,GoalQuantityin,  '
        +' GoalQuantityOut,AmountIn,AmountOut ) '
        +' select date,BillMode,warehouseID,GoodsID,GoalUnitID,'
        +' GoalQuantityin, GoalQuantityOut,AmountIn,AmountOut '
        +' FROM ('
        +' select b.recordstate,b.BillMode,b.date ,b.warehouseID,GoodsID, '
        +' GoalUnitID, 0.00 as GoalQuantityin,  '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantityOut,'
        +' 0.00 as AmountIn, b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as AmountOut from  SLGoodsOutDetail a '
        +' left outer join SLGoodsOutMaster b on b.ID=a.MasterID ' //銷售出庫
        +' UNION ALL '
        +' select b.recordstate,b.BillMode,b.date ,b.warehouseID,GoodsID,'
        +' GoalUnitID,0.00 as GoalQuantityin,  '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantityOut,'
        +' 0.00 as AmountIn,b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as AmountOut from  YDGoodsOutDetail a  '
        +' left outer join YDGoodsOutMaster b on b.ID=a.MasterID   ' //生產出庫
        +' UNION ALL '
        +' select b.recordstate,b.BillMode,b.date ,b.warehouseID,GoodsID,'
        +' GoalUnitID,0.00 as GoalQuantityin,  '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantityOut,'
        +' 0.00 as AmountIn,b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as AmountOut from  STGoodsCountOffDetail a  '
        +' left outer join STGoodsCountOffMaster b on b.ID=a.MasterID   '
        +' where  b.BillMode<>'+Quotedstr('庫存盤盈')   //庫存盤點表 (盤虧\生產損耗倉庫)
        +' UNION ALL '
        +' select b.recordstate,b.BillMode,b.date ,b.ClientID as warehouseID,GoodsID,GoalUnitID,'
        +'  0.00 as GoalQuantityin,  '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantityOut,'
        +' 0.00 as AmountIn,b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as AmountOut from  STGoodsOutInDetail a    '
        +' left outer join STGoodsOutInMaster b on b.ID=a.MasterID   ' //調撥出庫
        +' UNION ALL '
        +' select b.recordstate,b.Billmode,b.date ,b.warehouseID,GoodsID,GoalUnitID,   '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantityIn, '
        +' 0.00 as GoalQuantityOut,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as AmountIn, 0.00 as AmountOut from PCGoodsinDetail a   '
        +' left outer join PCGoodsInMaster b on b.ID=a.MasterID ' //采購入庫
        +' UNION ALL '
        +' select b.recordstate,b.Billmode,b.date ,b.warehouseID,GoodsID,GoalUnitID,   '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantityIn, '
        +' 0.00 as GoalQuantityOut,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as AmountIn, 0.00 as AmountOut from YDGoodsInDetail a   '
        +' left outer join YDGoodsInMaster b on b.ID=a.MasterID ' //生產入庫
        +' UNION ALL '
        +' select b.recordstate,b.Billmode,b.date ,b.warehouseID,GoodsID,GoalUnitID,   '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantityIn, '
        +' 0.00 as GoalQuantityOut,'
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as AmountIn, 0.00 as AmountOut from STGoodsOutInDetail a   '
        +' left outer join STGoodsOutInMaster b on b.ID=a.MasterID ' //調撥入庫
        +' UNION ALL '
        +' select b.recordstate,b.Billmode,b.date ,b.warehouseID,GoodsID,GoalUnitID,   '
        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.GoalQuantity AS GoalQuantityIn, '
        +' 0.00 as GoalQuantityOut,'