unit STClothStockRep;

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, GEdit,
  DBCtrls, Menus, WNADOCQuery,TypInfo, CheckLst;

type
  TSTClothStockRepForm = class(TWSVoucherBrowseForm)
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton7: TToolButton;
    ToolButton8: TToolButton;
    ToolButton9: TToolButton;
    ToolButton11: TToolButton;
    ToolButton12: TToolButton;
    ToolButton13: TToolButton;
    adsMaster: TADODataSet;
    Panel2: TPanel;
    ADOQuery: TADOQuery;
    ADOQuery2: TADOQuery;
    Panel3: TPanel;
    Panel5: TPanel;
    Button3: TButton;
    TempAds: TADODataSet;
    WNADOCQuery1: TWNADOCQuery;
    Button1: TButton;
    Panel4: TPanel;
    GroupBox3: TGroupBox;
    Memo1: TMemo;
    Button2: TButton;
    Panel6: TPanel;
    Button4: TButton;
    ExpSttcGroupBox: TGroupBox;
    ExpSttcCheckListBox: TCheckListBox;
    ExpSttcCkBxPopMenu: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N3: TMenuItem;
    N4: TMenuItem;
    GroupBox1: TGroupBox;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    ListGoodsDetailAct: TAction;
    ListGoodsDetail: TMenuItem;
    VipsetPanel: TPanel;
    Label1: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    Button5: TButton;
    Button6: TButton;
    ToolButton1: TToolButton;
    GoodsFlowListAct: TAction;
    AccountListQry: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    AccountListQryDSDesigner: TDateTimeField;
    AccountListQryDSDesigner2: TStringField;
    AccountListQryDSDesigner3: TStringField;
    AccountListQryDSDesigner4: TStringField;
    AccountListQryDSDesigner5: TStringField;
    AccountListQryDSDesigner6: TStringField;
    AccountListQryDSDesigner7: TStringField;
    AccountListQryDSDesigner8: TStringField;
    AccountListQryDSDesigner9: TStringField;
    AccountListQryDSDesigner10: TBCDField;
    AccountListQryDSDesigner11: TBCDField;
    AccountListQryDSDesigner12: TBCDField;
    AccountListQryDSDesigner13: TStringField;
    AccountListQryDSDesigner14: TBCDField;
    AccountListQryDSDesigner15: TBCDField;
    AccountListQryDSDesigner16: TBCDField;
    AccountListQryDSDesigner17: TBCDField;
    AccountListQryDSDesigner18: TBCDField;
    AccountListQryDSDesigner19: TStringField;
    AccountListQryDSDesigner20: TBCDField;
    AccountListQryDSDesigner21: TBCDField;
    AccountListQryDSDesigner22: TBCDField;
    AccountListQryDSDesigner23: TBCDField;
    AccountListQryDSDesigner24: TBCDField;
    ToolButton2: TToolButton;
    ToolButton3: TToolButton;
    DeadStockAct: TAction;
    procedure UpdateDBGrid;
    procedure DBGridTitleClick(Column: TColumn);
    procedure DBGridDblClick(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure N4Click(Sender: TObject);
    procedure N3Click(Sender: TObject);
    procedure adsMasterBeforeOpen(DataSet: TDataSet);
    procedure ExpSttcCheckListBoxClickCheck(Sender: TObject);
    procedure ListGoodsDetailActExecute(Sender: TObject);
    procedure GoodsFlowListActExecute(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure DateTimePicker1CloseUp(Sender: TObject);
    procedure DateTimePicker2CloseUp(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure adsMasterAfterOpen(DataSet: TDataSet);
    procedure DeadStockActExecute(Sender: TObject);

  private
    { Private declarations }
    WhereStr,SelectStr :string;
  public
    { Public declarations }
  protected
    function CreateEditForm: TWSEditForm; override;
  end;

var
  STClothStockRepForm: TSTClothStockRepForm;

implementation

uses CommonDM,QLDBFlt,QLRptBld,SLRpCheckReckoning;

{$R *.dfm}

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


procedure TSTClothStockRepForm.UpdateDBGrid;
var I: Integer;
begin
  with DBGrid do
  begin
    FooterRowCount := 0;
    Columns[0].Footer.ValueType := fvtStaticText;
    Columns[0].Footer.Value := '合計:';
    Columns[0].Footer.Alignment := taCenter;
    Columns[0].Title.Alignment:= taCenter;
    for I := 0 to Columns.Count - 1 do
    begin
      Columns[i].Width :=120;
      if Pos('日',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      if Pos('編',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      Columns[i].Title.Alignment:= taCenter;
      if Columns[I].Field is TNumericField then
      begin
        if (not CheckBox1.Checked) and (Pos('數量',Columns[I].FieldName)>0 )
             then Columns[I].Visible :=False;
        if (not CheckBox2.Checked) and  (Pos('單價',Columns[I].FieldName)>0 )
             then Columns[I].Visible :=False;
        if (not CheckBox3.Checked) and  (Pos('金額',Columns[I].FieldName)>0 )
             then Columns[I].Visible :=False;
        SetStrProp(Fields[I], 'DisplayFormat','#,#.00') ;
        if (Pos('單價',Columns[I].FieldName)<=0)   then
           Columns[I].Footer.ValueType := fvtSum;
      end;
    end;
    FooterRowCount := 1;
  end;
end;

procedure TSTClothStockRepForm.DBGridTitleClick(Column: TColumn);
begin
  inherited;
  UpdateDBGrid;
end;

procedure TSTClothStockRepForm.DBGridDblClick(Sender: TObject);
begin
// inherited;
end;


procedure TSTClothStockRepForm.Button3Click(Sender: TObject);
begin
  inherited;
  WNADOCQuery1.TabName :='TempExpenseList';
  WNADOCQuery1.ConnectionString :=CommonData.acnConnection.ConnectionString;
  WNADOCQuery1.Execute(False);
  if  trim(WNADOCQuery1.QueryTerm)<>'' then
      WhereStr :=' where ' +  trim(WNADOCQuery1.QueryTerm)
      else WhereStr :=' Where 1=1 ' ;
  Memo1.ReadOnly :=False;
  Memo1.Clear;
  if ( copy(trim(WNADOCQuery1.QueryTerm),1,3) ='not' ) or
    ( copy(trim(WNADOCQuery1.QueryTerm),1,3) ='NOT' )then
     Memo1.Text :='全部不滿足條件: '
     +Copy(trim(WNADOCQuery1.ShowTerm.Text),5,length(trim(WNADOCQuery1.ShowTerm.Text))-4 )
     else Memo1.Text :=Copy(trim(WNADOCQuery1.ShowTerm.Text),5,length(trim(WNADOCQuery1.ShowTerm.Text))-4);
  if WhereStr =' Where 1=1 ' then
    begin
      Memo1.Clear;
      Memo1.Text :='不限制條件!';
    end;
  Memo1.ReadOnly :=True;
  ToolBar.Hint :=' 臺帳條件: '+Memo1.Text;
  if Button1.Tag=1 then Button1Click(sender)
    else Button2Click(sender);
end;

procedure TSTClothStockRepForm.FormShow(Sender: TObject);
var I :integer;
begin
  inherited;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseList')
        +' )) DROP TABLE #ExpenseList ' ;
  ADOQuery.ExecSQL;

//銷售出庫
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select b.Date [日期], '
    +' b.Code [編號],                        '
    +' b.BillMode [業務類別],                '
    +' d.name  [經手人] ,                    '
    +' c.name [倉庫名稱],                    '
    +' h.name [商品類別],                    '
    +' e.name [商品名稱],                    '
    +' e.Code [商品編號],                    '
    +' g.name [標準單位],                    '
    +' a.GoodsSpec [顏色],               '
    +' a.CapStyle [杯型],               '
    +' Isnull(a.SizeA,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫Ｓ],  '   //
    +' Isnull(a.SizeB,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫Ｍ],  '
    +' Isnull(a.SizeC,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫Ｌ],  '
    +' Isnull(a.SizeD,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫XL],  '
    +' Isnull(a.SizeE,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫XXL],  '
    +' Isnull(a.SizeF,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫XXXL],  '
    +' Isnull(a.QuantityPcs,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫均碼],                  '

    +' a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫標準數量],  '
    +' a.PriceGoal [入庫標準單價],                '
    +' a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫金額], '

//    +' f.name     [入庫包裝單位],                '
//    +' a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫包裝數量],     '
//    +' a.PriceBase [入庫包裝單價]  ,             '


    +' Isnull(a.SizeA,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫Ｓ],  '   //
    +' Isnull(a.SizeB,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫Ｍ],  '
    +' Isnull(a.SizeC,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫Ｌ],  '
    +' Isnull(a.SizeD,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫XL],  '
    +' Isnull(a.SizeE,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫XXL],  '
    +' Isnull(a.SizeF,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫XXXL],  '
    +' Isnull(a.QuantityPcs,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫均碼], '

    +' a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫標準數量],  '
    +' a.PriceGoal [出庫標準單價],                '
    +' a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫金額] '

//    +' f.name     [出庫包裝單位],                '
//    +' a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [出庫包裝數量],     '
//    +' a.PriceBase [出庫包裝單價]                '
    +' into #ExpenseList '
    +' from SLSaleDetail a                   '
    +' left outer join SLSaleMaster     b on b.ID=a.MasterID      '
    +' left outer join STWarehouse         c on c.ID=b.WareHouseID      '
    +' left outer join MSEmployee       d on d.ID=b.EmployeeID    '
    +' left outer join DAGoods          e on e.ID=a.GoodsID       '
    +' left outer join MSUnit           f on f.ID=a.PackUnitID    '
    +' left outer join MSUnit           g on g.ID=E.UnitID    '
    +' left outer join DAGoodsClass     h on h.ID=e.GoodsClassID  '
    +' where b.RecordState<>'+ Quotedstr('刪除')
    +' and isnull(a.GoodsID,0) <>0 '
    +' order by  b.date ,b.code,a.ID ';
  ADOQuery.ExecSQL;
//銷售出庫

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Update  #ExpenseList set [入庫Ｓ]=null , '
    +' [入庫Ｍ]=null, [入庫Ｌ]=null , [入庫XL]=null ,    '
    +' [入庫XXL]=null,[入庫XXXL]=null, [入庫均碼]=null,  '
    +' [入庫標準數量]=null,[入庫標準單價]=null ,[入庫金額]=null ';
  ADOQuery.ExecSQL;

  //采購入庫--
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into  #ExpenseList (     '
    +' [日期], [編號],[業務類別],[經手人] , [倉庫名稱],  '
    +' [商品類別], [商品名稱],[商品編號],[標準單位],[顏色],[杯型], '
    +' [入庫Ｓ] ,[入庫Ｍ],[入庫Ｌ] , [入庫XL] ,    '
    +' [入庫XXL],[入庫XXXL], [入庫均碼],            '
    +' [入庫標準數量],[入庫標準單價] ,[入庫金額] ) '
    +' select b.Date [日期],   '
    +' b.Code [編號],                        '
    +' b.BillMode [業務類別],                '
    +' d.name  [經手人] ,                    '
    +' c.name [倉庫名稱],                    '
    +' h.name [商品類別],                    '
    +' e.name [商品名稱],                    '
    +' e.Code [商品編號],                    '
    +' g.name [標準單位],                    '
    +' a.GoodsSpec [顏色],               '
    +' a.CapStyle [杯型],               '
    +' Isnull(a.SizeA,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫Ｓ],  '   //
    +' Isnull(a.SizeB,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫Ｍ],  '
    +' Isnull(a.SizeC,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫Ｌ],  '
    +' Isnull(a.SizeD,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫XL],  '
    +' Isnull(a.SizeE,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫XXL],  '
    +' Isnull(a.SizeF,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫XXXL],  '
    +' Isnull(a.QuantityPcs,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫均碼],         '
    +' Isnull(a.GoalQuantity,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫標準數量],    '
    +' a.PriceGoal [入庫標準單價],                '
    +' Isnull(a.Amount,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫金額]              '
    +' from PCPurchaseDetail a                   '
    +' left outer join PCPurchaseMaster     b on b.ID=a.MasterID      '
    +' left outer join STWarehouse         c on c.ID=b.WareHouseID      '
    +' left outer join MSEmployee       d on d.ID=b.EmployeeID    '
    +' left outer join DAGoods          e on e.ID=a.GoodsID       '
    +' left outer join MSUnit           f on f.ID=a.PackUnitID    '
    +' left outer join MSUnit           g on g.ID=E.UnitID    '
    +' left outer join DAGoodsClass     h on h.ID=e.GoodsClassID  '
    +' where b.RecordState<>'+ Quotedstr('刪除')
    +' and isnull(a.GoodsID,0) <>0 '
    +' order by  b.date ,b.code,a.ID ';
  ADOQuery.ExecSQL;
//采購入庫--

//生產入庫==
  {
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into  #ExpenseList ( '
    +' [日期], [編號],[業務類別],[經手人] , [倉庫名稱], '
    +' [商品類別], [商品名稱],[規格型號],[標準單位],'
    +' [入庫標準數量], [入庫標準單價],[入庫金額],[入庫包裝單位],    '
    +' [入庫包裝數量], [入庫包裝單價]  )'
    +' select b.Date [日期], '
    +' b.Code [編號],                        '
    +' b.BillMode [業務類別],                '
    +' d.name  [經手人] ,                    '
    +' c.name [倉庫名稱],                    '
    +' h.name [商品類別],                    '
    +' e.name [商品名稱],                    '
    +' a.GoodsSpec [規格型號],               '
    +' g.name [標準單位],                    '
    +' a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫標準數量],  '
    +' a.PriceGoal [入庫標準單價],                '
    +' a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [入庫金額],                  '
    +' f.name     [入庫包裝單位],                '