unit PCPurchaseFruitList;

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,TypInfo,
  Menus;

type
  TPCPurchaseFruitListForm = class(TWSVoucherBrowseForm)
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton7: TToolButton;
    ToolButton8: TToolButton;
    ToolButton9: TToolButton;
    ToolButton11: TToolButton;
    ToolButton12: TToolButton;
    ToolButton13: TToolButton;
    adsMaster: TADODataSet;
    ADOQuery: TADOQuery;
    Panel2: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    BitBtn6: TBitBtn;
    BitBtn7: TBitBtn;
    BitBtn8: TBitBtn;
    BitBtn9: TBitBtn;
    BitBtn10: TBitBtn;
    BitBtn11: TBitBtn;
    BitBtn12: TBitBtn;
    BitBtn13: TBitBtn;
    BitBtn14: TBitBtn;
    ToolButton1: TToolButton;
    procedure FormShow(Sender: TObject);
    procedure DBGridCellClick(Column: TColumn);
    procedure DBGridDblClick(Sender: TObject);
    procedure UpdateDBGrid;
    procedure DateTimePicker1Change(Sender: TObject);
    procedure DateTimePicker2Change(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure BitBtn8Click(Sender: TObject);
    procedure BitBtn9Click(Sender: TObject);
    procedure BitBtn10Click(Sender: TObject);
    procedure BitBtn12Click(Sender: TObject);
    procedure BitBtn11Click(Sender: TObject);
    procedure BitBtn13Click(Sender: TObject);
    procedure BitBtn14Click(Sender: TObject);
    procedure adsMasterBeforeOpen(DataSet: TDataSet);
    procedure adsMasterAfterOpen(DataSet: TDataSet);

  private
    year,month,day,year1,month1,day1 :word;
    Datestr1,Datestr2 :string;
    { Private declarations }
  public
    { Public declarations }
  protected
    function CreateEditForm: TWSEditForm; override;
  end;

var
  PCPurchaseFruitListForm: TPCPurchaseFruitListForm;

implementation

uses CommonDM ;

{$R *.dfm}

function TPCPurchaseFruitListForm.CreateEditForm: TWSEditForm;
begin
//  Result :=;
end;


procedure TPCPurchaseFruitListForm.FormShow(Sender: TObject);
begin
  inherited;
  DecodeDate(date,year,month,day)  ;
  DateTimePicker1.Date :=Encodedate(year,month,1);
  DateTimePicker2.Date :=EndoftheMonth(date);
  DecodeDate(DateTimePicker1.Date,year, month, day);
  DecodeDate(DateTimePicker2.Date,year1, month1, day1);
  DateStr1 :=Datetostr(DateTimePicker1.Date);
  DateStr2 :=Datetostr(DateTimePicker2.Date);
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#PCPurchaseFruit')
        +' )) DROP TABLE #PCPurchaseFruit ' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text := ' CREATE TABLE #PCPurchaseFruit ( '
        +' [ID] [int] IDENTITY (1, 1) NOT NULL , '
        +' [RecordState] [Varchar] (12) NULL , '
        +' [Date] [datetime] NULL ,'
        +' [code] [int] NULL , '
        +' [TimePeriod] [varchar] (16)  NULL ,'
        +' [BillMode] [varchar] (16)  NULL , '
        +' [EmployeeID] [int] NULL ,	'
        +' [ClientID] [int] NULL , '
        +' [AreaID] [int] NULL , '
        +' [AreaIDT] [int] NULL , '
        +' [GoodsClassID] [int] NULL , '
        +' [AreaLevel] [varchar] (30)  NULL , '
        +' [Client] [varchar] (60)  NULL , '
        +' [Employee] [varchar] (60)  NULL , '
        +' [Goods] [varchar] (60)  NULL , '
        +' [Area] [varchar] (60)  NULL , '
        +' [AreaT] [varchar] (60)  NULL , '
        +' [GoodsClass] [varchar] (60)  NULL , '
        +' [GoodsClassT] [varchar] (60)  NULL , '
        +' [GoalUnit] [varchar] (60)  NULL , '
        +' [GoodsClassIDT] [int] NULL , '
        +' [GoodsID] [int] NULL ,'
        +' [GoodsClassLevel] [varchar] (30)  NULL ,'
        +' [GoodsSpec] [varchar] (30) NULL ,'
        +' [Quantity] [float] NULL , '
        +' [PackUnitID] [int] NULL , '
        +' [PriceBase] [float] NULL ,'
        +' [Amount] [float] NULL ,'
        +' [GoalUnitID] [int] NULL ,'
        +' [GoalQuantity] [float] NULL ) ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' truncate table  #PCPurchaseFruit ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into  #PCPurchaseFruit ( '
      +' ClientID,EmployeeID,date,code,billmode,GoodsID, GoodsSpec,'
      +' Quantity,PackUnitID,PriceBase,Amount,GoalUnitID,'
      +' GoalQuantity,AreaID,GoodsClassID,TimePeriod )'
      +' select ClientID,EmployeeID,b.date,b.code,b.billmode,     '
      +' GoodsID, GoodsSpec ,                  '
      +' Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) as Quantity ,'
      +' PackUnitID,PriceBase,                   '
      +' Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) as Amount ,      '
      +' GoalUnitID,                               '
      +' GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) as GoalQuantity, '
      +' c.AreaID ,d.GoodsClassID,b.date'
      +' from PCPurchaseDetail a                            '
      +' left outer join PCPurchaseMaster b on b.ID=a.MasterID '
      +' left outer join DAClient c on c.ID=b.ClientID'
      +' left outer join DAGoods d on d.ID=a.GoodsID '
      +' where b.RecordState<>'+Quotedstr('刪除');
  ADOQuery.ExecSQL;
  
  ADOQuery.Close;
  ADOQuery.SQL.Text := ' update  #PCPurchaseFruit set TimePeriod=date' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text := ' update  #PCPurchaseFruit set  '
      +' TimePeriod=Rtrim(Ltrim(SUBSTRING(Rtrim(Ltrim(TimePeriod)),12,12)))';
  ADOQuery.ExecSQL;

  ADOQuery.Close;        //獲取地區(qū)的levelcode
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit  '
      +' SET #PCPurchaseFruit.AreaLevel = DAArea.LevelCode '
      +' FROM #PCPurchaseFruit left outer  JOIN DAArea '
      +' ON (#PCPurchaseFruit.AreaID = DAArea.ID) ' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取商品的levelcode
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.GoodsClassLevel = DAGoodsClass.LevelCode '
      +' FROM #PCPurchaseFruit left outer  JOIN DAGoodsClass           '
      +' ON (#PCPurchaseFruit.GoodsClassID = DAGoodsClass.ID)               ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text := ' update  #PCPurchaseFruit set  '
      +' AreaLevel=SUBSTRING(Rtrim(Ltrim(AreaLevel)),1,3) ,'
      +' GoodsClassLevel=SUBSTRING(Rtrim(Ltrim(GoodsClassLevel)),1,3) ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;        //獲取地區(qū)的新的地區(qū)ID
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit  '
      +' SET #PCPurchaseFruit.AreaIDT = DAArea.ID '
      +' FROM #PCPurchaseFruit left outer  JOIN DAArea '
      +' ON (#PCPurchaseFruit.AreaLevel = DAArea.LevelCode) ' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取商品的levelcode
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.GoodsClassIDT = DAGoodsClass.ID '
      +' FROM #PCPurchaseFruit left outer  JOIN DAGoodsClass           '
      +' ON (#PCPurchaseFruit.GoodsClassLevel = DAGoodsClass.LevelCode)               ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取客戶名稱
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.Client = DAClient.name'
      +' FROM #PCPurchaseFruit left outer  JOIN DAClient           '
      +' ON (#PCPurchaseFruit.ClientID = DAClient.ID)    ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取業(yè)務(wù)員姓名
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.Employee = MSEmployee.name'
      +' FROM #PCPurchaseFruit left outer  JOIN MSEmployee           '
      +' ON (#PCPurchaseFruit.EmployeeID = MSEmployee.ID)    ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取商品
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.Goods = DAGoods.name'
      +' FROM #PCPurchaseFruit left outer  JOIN DAGoods           '
      +' ON (#PCPurchaseFruit.GoodsID = DAGoods.ID)    ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取地區(qū)
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.Area = DAArea.name'
      +' FROM #PCPurchaseFruit left outer  JOIN DAArea           '
      +' ON (#PCPurchaseFruit.AreaID = DAArea.ID)    ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取地區(qū)匯總
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.AreaT = DAArea.name'
      +' FROM #PCPurchaseFruit left outer  JOIN DAArea           '
      +' ON (#PCPurchaseFruit.AreaIDT = DAArea.ID)    ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取商品類別名稱
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.GoodsClass = DAGoodsClass.name'
      +' FROM #PCPurchaseFruit left outer  JOIN DAGoodsClass           '
      +' ON (#PCPurchaseFruit.GoodsClassID = DAGoodsClass.ID)    ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取商品匯總類別名稱
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.GoodsClassT = DAGoodsClass.name'
      +' FROM #PCPurchaseFruit left outer  JOIN DAGoodsClass           '
      +' ON (#PCPurchaseFruit.GoodsClassIDT = DAGoodsClass.ID)    ';
  ADOQuery.ExecSQL;

  ADOQuery.Close; //獲取商品匯總類別名稱
  ADOQuery.SQL.Text :=' UPDATE #PCPurchaseFruit '
      +' SET #PCPurchaseFruit.GoalUnit = MSUnit.name'
      +' FROM #PCPurchaseFruit left outer  JOIN MSUnit           '
      +' ON (#PCPurchaseFruit.GoalUnitID = MSUnit.ID)    ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  //--期初數(shù)據(jù)插入完畢
  BitBtn6Click(sender);
end;

procedure TPCPurchaseFruitListForm.DBGridCellClick(Column: TColumn);
begin
  UpdateDBGrid;
end;